BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser61
Calcite | Level 5

Hello,

 

I am trying to calculate a weighted geomean such that:

CityProvincePrice_RelativeWeightWeighted_Geomean
MontrealQC1.003210.651.003538
QuebecQC1.003680.201.003538
GatineauQC1.004770.151.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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

5 REPLIES 5
ballardw
Super User

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
Calcite | Level 5
Thanks so much ballardw! I really liked this option as well!
PGStats
Opal | Level 21

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
sasuser61
Calcite | Level 5
Thanks so much PGStats, this is exactly what I was looking for!
Ksharp
Super User

Or using PROC UNIVARIATE :

 

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

var weight;

weight height;

run;

 

Check want table.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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