In my previous blog, I showed you how to calculate response rates using SPSS by a subgroup. As some of you probably noticed, a Total row was missing. That’s because it’s a little more complicated, but not difficult to program. Of course, one can perform this calculation in EXCEL, but SPSS can also perform this task better, faster (just like Steven Austin, 6 million $ man!). To create this Total information, we’ll be using the AGGREGATE command and Merge files.
Below are 10 responses from three departments.
First, we’ll Aggregate the file by Department (/Break=Dept) and create a new dataset, called One.
DATASET DECLARE One.
Making sure this new dataset is the Active Dataset, create a population variable, representing the total number of employees in each department.
DATASET ACTIVATE One.
If (dept=1) Population=20.
If (dept=2) Population=10.
If (dept=3) Population=15.
Now, create another dataset which contains the sum of the Population and Respondents. Notice the /Break command.
DATASET DECLARE Two.
Using the Compute command, create a variable called Dept with a value of 4, representing all respondents. (We’ll add the label later.)
The Two dataset will look like this:
Next, merge the two files together. The active dataset is the first Aggregate file created and we are appending the one line dataset called Two.
DATASET ACTIVATE One.
ADD FILES /FILE=*
Now, we’ll add the Value label for the value of 4. (Use Add Value Label or else the labels of the first three departments will have no label!)
Add Value labels
Next, calculate the response rate by dividing the number of respondents by the population and multiplying times 100.
Optional code to format Population:
Format Population (F4.0).
Your final data file, called One, will look like this.
Now, using Customer Table, generate the final results.
/VLABELS VARIABLES=Dept Respondents Population Resp_Rate DISPLAY=LABEL
/TABLE Dept [C] BY Population [S][MEAN] + Respondents [S][MEAN] + Resp_Rate [S][MEAN 'Response Rate' F40.2]
/CATEGORIES VARIABLES=Dept ORDER=A KEY=VALUE EMPTY=INCLUDE
TITLE='Response Rates by Department and Overall'.
While this dataset was very small, the exact same program will work on datasets comprising hundreds or
thousands of records. And, to think, writing and executing this program code didn’t cost us 6 million dollars!