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

[PQK]

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.

[Ksharp]

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

[Reeza]

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 col:);

    harmean=harmean(of col:);

    geomean=geomean(of col:);

run;

[Reeza · Accepted solution]

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;

[PQK]

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!