Cross Validated is a question and answer site for people interested in statistics, machine learning, data analysis, data mining, and data visualization. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'd be too happy, if someone could post a code snippet, which explains how to compute mean and variance of a set of records, which contain frequencies?

Suppose we have records like (FORMAT F)

- GroupA, 6 x Grade 1, 5 x Grade 2, 10 x Grade 3
- GroupB, 2 x Grade 1, 7 x Grade 2, 18 x Grade 3
- GroupA, 23 x Grade 1, 5 x Grade 2, 1 x Grade 3
- GroupA, 10 x Grade 1, 15 x Grade 2, 12 x Grade 3

Goal: Compute for each Group's mean and variance of grades using SPSS syntax.


Using SPS, processing cases (FORMAT C) would be simple (as colleagues told me):

# Record number 1 (line 1 of the above data)
GroupA, Grade 1
   ... 6 times
GroupA, Grade 1
GroupA, Grade 2
   ... 5 times
GroupA, Grade 2
GroupA, Grade 3
   ... 10 times
GroupA, Grade 3
# Record number 2 (line 2 of the above data) 
GroupB, Grade 1
GroupB, Grade 1
GroupB, Grade 2
   ... 78 times
GroupB, Grade 7
   ... and so on

Unfortunately, we don't have cases of FORMAT C, but already aggregated data as in FORMAT F.

I probably should add this:

I implemented a solution, which uses (user entered) FORMAT F data using PHP/SQL: The PHP program constructs an SQL query, which computes the mean, the variance and the standard deviation of each Group.

I even tested the solution using well-known sample data. The results are identical. Thus I'm sure the solution is correct.

Since the computed statistical properties are sensible, I'd like to get a verification using a standard statistical package using the real FORMAT F data (not the test case data).

My colleague is going to use SPSS.

share|improve this question
look up the AGGREGATE command in the help menu – Andy W May 11 '11 at 1:01
@Andy W: Thank you for the hint. As far as I understand - I'm no SPSS pro - AGGREGATE allows to aggregate certain cases and compute statistics over these grouped cases. The actual problem is how to use frequency data as the base of a SPSS computation. Each of the above records is itself somehow aggregated data. I could write a program in another program and prepare - based of the above data - individual cases. But how do I use the aggregates as a basis of an SPSS computation? – SteAp May 12 '11 at 19:38
@Stefan, It is still not clear what you are asking to do. If you could provide an example of what your data matrix looks like and what statistics you would like to compute that would help. – Andy W May 12 '11 at 20:12
@Andy W: Thanks for pointing out! Tried to describe the problem better. Sorry, non-native EN speaker - and non-SPSS user. – SteAp May 12 '11 at 20:22
You can compute the variance directly without reshaping your data, but if you simply want to reshape the data into format c and then use the in-house functions me and Bruce Weaver recently answered a question on the google group SPSS forum,…, that explains how you would do so. (the only difference is that you would need to do three seperate loop and xsave commands and then add the data files back together. – Andy W May 12 '11 at 20:36

I will to demonstrate how to do this without reshaping the data, as all it entails is simple arithmetic. If I am reading your question correctly, the data look something like this;

Data List Free / Group (A1) Grade1 Grade2 Grade3.
Begin Data
A 1 2 3
A 6 5 10
B 2 7 18
C 23 5 1
D 7 7 13
End Data.

To calculate any type of variance, you need to assign some type of numeric values to your grade variables. Here I will assume that a value for Grade1 is the equivalent of a 95, Grade 2 is equal to an 85, and Grade3 is equal to a 75. All that one needs to do after this is to essentially plug in the values for the calculation of the variance. The Wikipedia page for standard deviation has an explicit example taking you through the steps. The only difference in the code below is that I need to multiply the squared differences by the number of observations within that specific grade variable.

compute row_sum = (Grade1*95)+(Grade2*85)+(Grade3*75).
compute row_n = Grade1+Grade2+Grade3.
compute row_mean = row_sum/row_n.

compute square_diff1 = (95 - row_mean)*(95 - row_mean).
compute square_diff2 = (85 - row_mean)*(85 - row_mean).
compute square_diff3 = (75 - row_mean)*(75 - row_mean).

compute row_variance = ( (Grade1*square_diff1) + (Grade2*square_diff2) + (Grade3*square_diff3) ) / (row_n).

The resulting variable, row_variance , is a calculation of the variance for the observations in the row. In your example given it suggested you would have groupings of variables, and hence would need to calculate not only the row variance, but the group variance. You can simply use the AGGREGATE command to sum the Grade variables within each group, and then follow the same steps above. An example would be;

  /Grade1 =SUM(Grade1) 
  /Grade2 =SUM(Grade2)
  /Grade3 =SUM(Grade3).

This will produce a new dataset named Grouped, in which you can calculate the row variance using the exact same code above.

As a note, you asked for the variance in your question. I suspect the sample standard deviation is another statistic you might be interested in. One can not simply take the square root of the variance I gave above though, as one divides by the number of observations minus one for the sample standard deviation. So if you were interested in the sample standard deviation in the row you could use the code below;

compute row_sd =SQRT( ( (Grade1*square_diff1) + (Grade2*square_diff2) + (Grade3*square_diff3) ) / (row_n - 1) ).

I doubt this is the simplest solution in terms of length of code. But I hope it is straightforward mathematically.

share|improve this answer
Thx! I'll pass the information along to my colleague. In fact, my PHP solution does just this ;-) – SteAp May 13 '11 at 21:31
The mathematics behind variance or standard deviation isn't a problem at all. I listened to a few math lectures ;-) – SteAp May 15 '11 at 22:30
@Stefan, It is still totally unclear what you want, or why this needs to be accomplished in SPSS as opposed to whatever language. Feel free to clarify your question if you are in search of something different. – Andy W May 16 '11 at 0:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.