Subscribe Bookmark RSS Feed

Calculating Group Means in a table

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.
4 REPLIES
dbailey

Community Trekker

Joined:

Jun 23, 2011

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
ms

Super User

Joined:

Jun 23, 2011

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

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

-SemiChemE
dbailey

Community Trekker

Joined:

Jun 23, 2011

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