3-way CROSSTABS in SPSS

Question

I have some data in SPSS that I would like to format in a particular way, but I can't seem to find a way to do it in the documentation.

I have data that consists of 10 question responses, Q1 to Q10, with a Q1 to Q10 for each value of a variable SPEAKER within a variable SESSION. For example, each session can have up to five speakers, for for session 1 I might have data in Q1 to Q10 for speaker 1 and 2, but nothing for the others. The raw data looks something like this:

001A0123012301B0123012301C         D         E         

So that's session 001 with speakers A through E where A and B have data (Q1 to Q10, one column each) and the rest don't. Notice that only four responses are possible on Q1 to Q10, and below I'll label these R1, R2, R3, and R4. Hopefully that's more detail than you're going to need.

I want to generate one table per session that looks something like this:

Session 001
  Question | Speaker 1 | Speaker 2 | Speaker 3 | Speaker 4 | Speaker 5
       R1       10           15          0           0            0
   Q1  R2        9            4          0           0            0
       R3        0            1          0           0            0
       R4        2            0          0           0            0 
  ---------------------------------------------------------------------
       R1      ...
   Q2  R2      ...
       R3      ...
       R4      ...

The documentation seems to suggest that

CROSSTABS Q1 TO Q10 BY SPEAKER BY SESSION

will do what I want, but it doesn't. Any ideas on how I can get the desired result?

ETA: For anyone it might help, here is the actual syntax I used, with a few details changed to remove identifying information of our client.

set printback=no.
set width = 80 / highres = off.
TITLE 'Speaker Evaluations'.

INPUT PROGRAM.
FILE HANDLE IN  /NAME='C:\Documents and Settings\gworley\Desktop\Surveys\hrfse\hrfse.sdf' .
DATA LIST FILE=IN FIXED RECORDS=1 /1 BATCH 1-3.
REPEATING DATA STARTS=4 / OCCURS=5
  /DATA= SPEAKER 1 (A) Q1 TO Q10 2-11.
END INPUT PROGRAM.

VARIABLE LABLES
 SPEAKER "Speaker" /
 Q1 "Speaker's knowledge of the subject" /
 Q2 "Speaker's effectiveness in communicating information"/
 Q3 'The usefulness of the information presented'/
 Q4 "Speaker's response to audience questions"/
 Q5 'The pace of this course or panel'/
 Q6 'Clarity and organization'/
 Q7 'Focus on up-to-date issues'/
 Q8 'Overall session rating'/
 Q9 "Would you recommend this speaker for next year's conference"/
 Q10 'Did this speaker try to sell their services'/.


Missing values Q1 to Q8 (4 thru high)/
           Q9 to Q10 (2 thru high).

VALUE LABELS
 SPEAKER 'A' 'Speaker 1' 'B' 'Speaker 2' 'C' 'Speaker 3' 'D' 'Speaker 4' 'E' 'Speaker 5' /
 Q1 to Q8 0 'Poor' 1 'Fair' 2 'Good' 3 'Excellent' /
 Q9 to Q10 0 'No' 1 'Yes'.

FREQUENCIES VARIABLES=Q1 to Q10 / STATISTICS=ALL.
CROSSTABS 
  /TABLES=Q1 to Q10 BY SPEAKER BY BATCH
  /CELLS= COUNT COLUMN  .

Answer 1

Hello,

first I think your raw data is not well strutured to be read by SPSS. To read it I added session n° before each speaker.

I Tried the following syntax file:

// "3wayCrosstab.txt" = 001A0213210231001B2102320232001C2132021203001D1122330010001E0232120121
FILE HANDLE W3
   /NAME="3wayCrosstab.txt"
   /MODE=IMAGE
   /LRECL=14.

DATA LIST FILE=W3 
   /SESSION 1-3 (A) SPEAKER 4 (A) Q1 5 Q2 6 Q3 7 Q4 8 Q5 9 Q6 10 Q7 11 Q8 12 Q9 13 Q10 14

VALUE LABELS Q1 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q2 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q3 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q4 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q5 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q6 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q7 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q8 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q9 0 "R1" 1 "R2" 2 "R3" 3 "R4"
VALUE LABELS Q10 0 "R1" 1 "R2" 2 "R3" 3 "R4"

VALUE LABELS /SPEAKER "A" "Speaker1" "B" "Speaker2" "C" "Speaker3" "D" "Speaker4" "E" "Speaker5"

CROSSTABS /TABLE=Q1 TO Q10 BY SPEAKER BY SESSION.

I got this:

    2.2(2) CROSSTABS.  Q1 by SPEAKER by SESSION [count].
    #=======================#============================================#========#
    #    SESSION            #                   SPEAKER                  |        #
    #                       #--------+--------+--------+--------+--------+        #
    #                     Q1#Speaker1|Speaker2|Speaker3|Speaker4|Speaker5|  Total #
    #-----------------------#--------+--------+--------+--------+--------+--------#
    #        001 R1         #     1.0|      .0|      .0|      .0|     1.0|     2.0#
    #            R2         #      .0|      .0|      .0|     1.0|      .0|     1.0#
    #            R3         #      .0|     1.0|     1.0|      .0|      .0|     2.0#
    #            Total      #     1.0|     1.0|     1.0|     1.0|     1.0|     5.0#
    #=======================#========#========#========#========#========#========#

    2.3 CROSSTABS.  Q2 by SPEAKER by SESSION [count].
    #=======================#============================================#========#
    #    SESSION            #                   SPEAKER                  |        #
    #                       #--------+--------+--------+--------+--------+        #
    #                     Q2#Speaker1|Speaker2|Speaker3|Speaker4|Speaker5|  Total #
    #-----------------------#--------+--------+--------+--------+--------+--------#
    #        001 R2         #      .0|     1.0|     1.0|     1.0|      .0|     3.0#
    #            R3         #     1.0|      .0|      .0|      .0|     1.0|     2.0#
    #            Total      #     1.0|     1.0|     1.0|     1.0|     1.0|     5.0#
    #=======================#========#========#========#========#========#========#
...