For scaled questions, the output of SPSS tables places the mean scores in a separate (adjacent) column to the counts, making them not as easy to read as those produced created from most Tables software packages. Also, the syntax is long and cumbersome to reproduce if you need to run many such tables.
To modify the tables to make them easy to read, you can use something like the below SPSS macro, which gives you “nets” and mean scores underneath each column:
DEFINE !Means5Table(row=!CHAREND(‘/’))
CTABLES
/VLABELS VARIABLES = !row gender DISPLAY=LABEL
/PCOMPUTE & mymean = EXPR(([1]+2*[2]+3*[3]+4*[4]+5*[5])/SUBTOTAL[1])
/PPROPERTIES & mymean LABEL = “Mean” FORMAT=COUNT F40.1
/PCOMPUTE & myTop = EXPR([4]+[5])
/PPROPERTIES & myTop LABEL = “Net: Top 2” FORMAT=COUNT F40
/PCOMPUTE & myBottom = EXPR([1]+[2])
/PPROPERTIES & myBottom LABEL = “Net: Bottom 2” FORMAT=COUNT F40
/TABLE !row BY gender [C][COUNT F40.0, COLPCT.COUNT PCT40.1]
/CATEGORIES VARIABLES = !row [5,4,3,2,1, SUBTOTAL=’Total – DKs’, 6, &myTop, &myBottom, &mymean] EMPTY = INCLUDE TOTAL = YES POSITION = after
/CATEGORIES VARIABLES=gender [1,2] EMPTY = INCLUDE TOTAL=YES
POSITION = after
!ENDDEFINE.
The macro is designed for variables on a 1 to 5 scale with 5 being high and 1 being low (although this can easily be altered to suit your data – after all it is a simple mean calculation!).
We use /PCOMPUTE to calculate both the mean (“mymean”). We also sum the top and bottom two boxes (“myTop“ and “myBottom”, respectively) to see the total score of the two highest score ratings of the variable. We then format them with the /PPROPERTIES command.
We can extend the break along the top in the macro as required by adding new variables after the gender variable in the /TABLE part of the syntax.
We call the macro by simply writing: !Means5Table row variable (we defined and named the macro at the beginning of it). The variable is the name of the variable you are analysing, and results in this kind of output:
We can then use this macro to create a whole set of tables which look very similar to those constructed by other Tables software packages (and indeed on other datasets).
Once the tables have been exported or copied into Excel we can remove the mean of the Column N% by an easy piece of VBA code.
Author: Paul Brown, Director
