Construct a table "manually" in the data editor

 

Download as .sps Shows as plain text

 

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
***** TABLES connot do what is needed, solution is to construct 
	the table 'manually'.SPS.

*(Q)
The past few months I have been working on an item analysis application
where I am trying to condense the formatting of
SPSS output.   I currently have variables that are called CI1, Q1, CI2,
Q2, CI3, Q3, etc...  The CI1, CI2, CI3, etc.. are variables
that indicate whether a variable is right or wrong and are coded

   -3=Blank       -2=Right   -1=Wrong

with value labels specified as such, i.e.,

value label ci1 -3'blank' -2'right' -1'wrong'.

The variables values for q1, q2, etc.. are 0, 1, 2, 3, 4,
where the values represent responses on a test.

The following tables code will almost produce the desired results I want:
variable label ci1 'q1' / ci2 'q2' /ci3 'q3'.
   * Table of Frequencies.
TABLES
  /FORMAT BLANK MISSING('.') /TABLES
  (LABELS)  BY
  ( ci1 + q1 + ci2 + q2 + ci3 + q3 + ci4 + q4 +
    ci5 + q5 + ci6 + q6 + ci7 + q7 + ci8 + q8 + ci9 + q9 +
    ci10 + q10 + ci11 + q11 + ci12 + q12 + ci13 + q13 +
    ci14 + q14 + ci15 + q15 + ci16 + q16 + ci17 + q17 +
    ci18 + q18 + ci19 + q19 + ci20 + q20)
  /STATISTICS COUNT ((F5.0) '' )
       CPCT ((PCT7.1) '') .

Once I get this table and transpose the rows and columns using the
pivot table feature I get the following output:

         Right Wrong Blank  0     1     2     3     4
    Q1    51    23
          68%   31%
                                  1     2    51     20
                                  1%    3%   69%    27%

    Q2    69    5
          93%   7%
                           69     5
                           93%    7%
    Q3    69    5		1
          93%   6%
                                  2     3    69
                                  3%    4%   93%

etc..

What I would really like to get is:

         Right Wrong Blank  0     1     2     3     4

    Q1    51    23                1     2    51     20
          68%   31%               1%    3%   69%    27%

    q2    69    5          69     5
          93%   7%         93%    7%

    q3    69    5       1         2     3    69
          93%   7%                3%    4%   93%



*(A) Posted to SPSSX-L on 2001/10/28 by rlevesque@videotron.ca.


* Create some data for illustration purposes.
INPUT PROGRAM.
VECTOR ci(20F8.0) /q(20F8.0).
LOOP cnt=1 TO 120.
LOOP #cnt=1 TO 20.
COMPUTE ci(#cnt)=TRUNC(UNIFORM(1)+.5)-3.
COMPUTE q(#cnt)=TRUNC(UNIFORM(4)+.5).
IF (UNIFORM(1)<.005) ci(#cnt)=-1.
END LOOP.
END CASE.
END LOOP.
END FILE.
END INPUT PROGRAM.


* Start the job.
* Reformat from wide to long.
LOOP q=1 TO 20.
COMPUTE answeri=ci(q).
COMPUTE answer=q(q).
XSAVE OUTFILE='c:\\temp\\temp.sav' /KEEP=q answer answeri.
END LOOP.
EXECUTE.
GET FILE='c:\\temp\\temp.sav'.
FORMATS q(F4.0).

* Disperse data to correct columns then calculate counts.
VECTOR q(5F2.0) /qi(3F2.0).
COMPUTE qi(answeri + 4)=1.
COMPUTE q(answer+1)=1.
AGGREGATE
  /OUTFILE=*
  /BREAK=q
  /qi1 TO qi3 = SUM(qi1 TO qi3) /q1 TO q5 = SUM(q1 TO q5) .

* Calculate percentages.
COMPUTE #sum1=SUM(qi1 TO qi3).
VECTOR nb=qi1 TO q5 /pc(8F8.2).
LOOP #=1 TO 8.
COMPUTE pc(#)=nb(#)/#sum1.
END LOOP.

* move percentages to same columns as counts.
VECTOR col=qi1 TO pc8 /c(8F8.2).
LOOP #=1 TO 2.
COMPUTE #base=(#-1)*8.
LOOP #idx=1 TO 8.
COMPUTE c(#idx)=col(#base + #idx).
END LOOP.
XSAVE OUTFILE='c:\\temp\\temp2.sav' /KEEP=q c1 TO c8.
END LOOP.
EXECUTE.
GET FILE='c:\\temp\\temp2.sav'.

* At this point, the required numbers are all available.
SUMMARIZE
  /TABLES=q c1 c2 c3 c4 c5 c6 c7 c8
  /FORMAT=VALIDLIST NOCASENUM TOTAL
  /TITLE='Case Summaries'
  /MISSING=VARIABLE
  /CELLS=COUNT .

* Only cosmetic adjustments are missing (show % signs in % lines).
* This requires creation of string variables.
VECTOR col(8A5) /c=c1 TO c8.
LOOP #=1 TO 8.
DO IF MOD($CASENUM,2)=1.
COMPUTE col(#)=STRING(c(#),F4.0).
ELSE.
COMPUTE col(#)=STRING(c(#)*100,PCT3.0).
END IF.
END LOOP.
EXECUTE.

VARIABLE LABEL col1 'Right' col2 'Wrong' col3 'Blank' 
	col4 '0' col5 '1' col6 '2' col7 '3' col8 '4'. 
SUMMARIZE
  /TABLES=q col1 TO col8
  /FORMAT=VALIDLIST NOCASENUM TOTAL
  /TITLE='Item Analysis'
  /MISSING=VARIABLE
  /CELLS=NONE.
Related pages

...

Navigate from here