CODEWITHSUNDEEP
sqlsas
lbos
lbos

Reputation: 3

SAS how to calculate a variable that is the mean of the values of all the other observations for each observation?

For example, the dataset a is 

id   x
1    15
2    25
3    35
4    45

I want to add a column y to dataset a, y being the average of x excluding the current id. so y_1 = (x_2+x_3+x_4)/3 = (25+35+45)/3.

Upvotes: 0

Views: 94

Answers (1)

Joe
Joe

Reputation: 63424

Easiest way to do it without SQL is to add the mean and the n to each row (use PROC MEANS, then merge on the values), and then use math to remove the current value. IE, if x_mean=(15+25+35+45)/4 = 30, and x=15, then 

x_mean_others = ((30*4)-15)/(4-1) = 105/3 = 35

Alternateively, in SQL, you can calculate it on the fly with the same idea.

proc sql;
  create table want as
    select x, (mean(x)*n(x) - x)/(n(x)-1) as y
    from have H
    ;
quit;

This takes advantage of SAS's automatic remerging, in something like SQL Server you'd need a WITH clause to make this work I imagine.

Upvotes: 6

Related Questions