topic Re: Calculating Group Means in a table in Discussions

Calculating Group Means in a table

Thu, 21 Apr 2011 22:04:38 GMT

Given the first two columns of the following data set:

Group Height GrpMean
Dog 55 55.66666667
Dog 65 55.66666667
Dog 47 55.66666667
Cat 31 29.75
Cat 28 29.75
Cat 25 29.75
Cat 35 29.75
Mouse 12 12.5
Mouse 13 12.5

Is there an easy way to generate the third column (Means for each group)? I realize I can make a "summary table" to generate the requested means (or about a half dozen other methods), but how do I get these means back into the original table, so I can use them for additional calculations? Note that my actual dataset consists of thousands of "Groups" and may not necessarily be sorted nicely as above.

Re: Calculating Group Means in a table

dbailey — Thu, 21 Apr 2011 22:27:06 GMT

I think proc sql will work. SAS allows calculations of group values without forcing the grouping.

data work.grouping_test;
input GroupID $ Height;
cards;
Dog 55
Dog 65
Dog 47
Cat 31
Cat 28
Cat 25
Cat 35
Mouse 12
Mouse 13
run;

proc sql;
create table work.example as
select groupid, height, avg(height) as AvgHeight
from work.grouping_test
group by groupid;
run;

produces
Cat 25 29.75
Cat 28 29.75
Cat 35 29.75
Cat 31 29.75
Dog 47 55.666666667
Dog 55 55.666666667
Dog 65 55.666666667
Mouse 13 12.5
Mouse 12 12.5

Re: Calculating Group Means in a table

ms — Thu, 21 Apr 2011 23:30:33 GMT

I would use Summary, then Update. Either manually or by script.
Example:

Re: Calculating Group Means in a table

Fri, 22 Apr 2011 01:12:21 GMT

MS,

That seems to do the trick. Thank you very much!

-SemiChemE

Re: Calculating Group Means in a table

dbailey — Fri, 22 Apr 2011 02:36:28 GMT

clearly...didn't see the jmp forum...sorry.