Help using Base SAS procedures

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Accepted Solution Solved
Reply
Contributor PQK
Contributor
Posts: 21
Accepted Solution

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Dear Forum,

I am trying to output the geometric and harmonic mean for one variable that is subsetted by two other variables. As a reference, I would like to calculate GEOMEAN and HARMEAN just as PROC MEANS would do in the following example.

proc means data=inputdata noprint maxdec=2;

var var3;

class var1 var2;

output out=outputdata Mean(var3)=Mean;

run;

PROC MEANS accomplishes this task easily for the arithmetic mean, but I am stuck trying to find a work around for geometric and harmonic mean…sigh.

Any suggestions would be greatly appreciated.


Accepted Solutions
Solution
‎12-15-2011 12:53 PM
Super User
Posts: 17,750

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Actually, Proc SQL is much clearer...

proc sql;

    create table summary as

    select sex, mean(height) as avg_height, count(height)/sum(1/height) as harm_mean, exp(mean(log(height))) as geom_mean

    from sashelp.class

    group by sex;

quit;

View solution in original post


All Replies
Super User
Posts: 9,662

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

I checked the documentation. there are not such options.

But at Function, I found these two functions to calculate these means.

data mean;
 set sashelp.class;
 geomean=geomean(age,weight,height);
 harmean=harmean(age,weight,height);
run;




Ksharp

Super User
Posts: 17,750

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

You can use proc transpose and the functions that KSharp has suggested, or use some transformations with proc means. I prefer the transformations as its easier for me to understand.

For geometric mean take the log of your variable and then exponentiate the resulting mean of the logged variable.

For harmonic mean take the inverse of your variable and then n/sum(inverse) is the number you're looking for.

data class;

    set sashelp.class;

    harm=1/height;

    geom=log(height);

run;

ods table summary=step1;

proc means data=class n mean sum;

class sex;

var height harm geom;

run;

data step2;

    set step1;

    geometric_mean=exp(geom_mean);

    harmonic_mean=harm_n/harm_sum;

    keep sex height_mean geometric_mean harmonic_mean;

run;

You can verify it using KSharps formulas:

proc sort data=class;

by sex;

run;

proc transpose data=class out=class2;

by sex;

var height;

run;

data class3;

    set class2;

    mean=mean(of colSmiley Happy;

    harmean=harmean(of colSmiley Happy;

    geomean=geomean(of colSmiley Happy;

run;

Solution
‎12-15-2011 12:53 PM
Super User
Posts: 17,750

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Actually, Proc SQL is much clearer...

proc sql;

    create table summary as

    select sex, mean(height) as avg_height, count(height)/sum(1/height) as harm_mean, exp(mean(log(height))) as geom_mean

    from sashelp.class

    group by sex;

quit;

Contributor PQK
Contributor
Posts: 21

Work around for GEOMEAN and HARMEAN not available in PROC MEANS?

Reeza,

Many thanks for the replies.

After my first attempt with PROC MEANS failed, I also tried to create a table within PROC SQL using ‘group by’ to subset accordingly, but it never worked. After looking at your expression for geometric mean [that is, exp(mean(log(height)))], I realize the expression I used was incorrect. For some reason the mathematic formula I created in excel doesn’t work in SAS. I think it has something to do with array formulas.

In any case, both your SQL and data step method work to produce the correct geometric and harmonic mean! I am not that familiar with the table ODS destination, so I think I will stick with the SQL statement method instead.

Thanks again for your help!Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 2774 views
  • 4 likes
  • 3 in conversation