How to calculate a weighted geomean

[sasuser61]

Hello,

I am trying to calculate a weighted geomean such that:

City	Province	Price_Relative	Weight	Weighted_Geomean
Montreal	QC	1.00321	0.65	1.003538
Quebec	QC	1.00368	0.20	1.003538
Gatineau	QC	1.00477	0.15	1.003538

Unfortunately, I can't seem to find a built in function that could perform this task. But, this is what I would love to be able to do (if it only existed):

proc sql;
create table want as

select *, sumproduct(price_relative ** weight) as weighted_geomean

from have

group by province;
quit;

Since sumproduct doesn't exist like it does in Excel, is there a simple work around? Or is there a built in function for weighted geomean that I just haven't come across?

Thanks so much in advance!

[ballardw]

Proc Surveymeans will calculated weighted geomeans.

See if this helps:

ods output domaingeomeans= mydataset;   
proc surveymeans data=have geomean;
   domain province;
   var   price_relative;
   weight weight;
run;

Surveymeans uses the ODS OUTPUT to create output data sets. Domaingeomeans is the table that would have the geomean value by domain (your desired group variable(s)). You would need to join the result back to your data and rename the default output variable name.

I really hope your weighted geomean is not actually expected to be the same for different provinces as shown in your "example".

[sasuser61]

Thanks so much ballardw! I really liked this option as well!

[PGStats · Accepted solution]

This is not a workaround, it is how to get the correct result:

proc sql;
create table want as
select *, exp(sum(log(price_relative)* weight) / sum(weight)) as weighted_geomean
from have
group by province;
quit;

(untested)

[sasuser61]

Thanks so much PGStats, this is exactly what I was looking for!

[Ksharp]

Or using PROC UNIVARIATE :

proc univariate data=sashelp.class outtable=want  noprint;

var weight;

weight height;

run;

Check want table.