topic Re: How to calculate a weighted geomean in SAS Programming

How to calculate a weighted geomean

sasuser61 — Thu, 23 Sep 2021 18:49:53 GMT

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!

Re: How to calculate a weighted geomean

ballardw — Thu, 23 Sep 2021 19:30:03 GMT

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".

Re: How to calculate a weighted geomean

PGStats — Thu, 23 Sep 2021 19:40:39 GMT

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)

Re: How to calculate a weighted geomean

sasuser61 — Thu, 23 Sep 2021 20:36:45 GMT

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

Re: How to calculate a weighted geomean

sasuser61 — Thu, 23 Sep 2021 20:37:23 GMT

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

Re: How to calculate a weighted geomean

Ksharp — Fri, 24 Sep 2021 12:41:15 GMT

Or using PROC UNIVARIATE :

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

var weight;

weight height;

run;

Check want table.