Calcite | Level 5

## How to calculate a weighted geomean

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: How to calculate a weighted geomean

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)

PG
5 REPLIES 5
Super User

## Re: How to calculate a weighted geomean

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

Calcite | Level 5

## Re: How to calculate a weighted geomean

Thanks so much ballardw! I really liked this option as well!
Opal | Level 21

## Re: How to calculate a weighted geomean

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)

PG
Calcite | Level 5

## Re: How to calculate a weighted geomean

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

## Re: How to calculate a weighted geomean

Or using PROC UNIVARIATE :

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

var weight;

weight height;

run;

Check want table.

Discussion stats
• 5 replies
• 569 views
• 6 likes
• 4 in conversation