CODEWITHSUNDEEP
sassas-macroproc-sql
Anthony Martin
Anthony Martin

Reputation: 787

SAS differences in outcome between sql and proc means

I want to have a weighted average of some variable in a macro variable. My var is zindi&aa and my weight is wprm&aa

I am trying to make sense of two ways of doing it :

one with a proc sql

proc sql noprint;
    select mean(zindi&aa. * wprm&aa.)/sum(wprm&aa.) into :Mean_zindi_aa
    from Panel(where=(annee&ap.<="&nais18" ));
quit;

it yields me an average of 0.77

one with proc means

proc means data=Panel(where=(annee&ap.<="&nais18" )); 
    var zindi&ap. ;
    weight wprm&ap ;
    output out=mean_zindi&ap mean=moy&ap;
run;

and then a data _null_ part

which yields an average of around 20200 that seems to be the correct one

so my question is

Upvotes: 0

Views: 114

Answers (2)

DomPazz
DomPazz

Reputation: 12465

Change your PROC SQL to:

proc sql noprint;
    select SUM(zindi&aa. * wprm&aa.)/sum(wprm&aa.) into :Mean_zindi_aa
    from Panel(where=(annee&ap.<="&nais18" ));
quit;

You need to SUM the product, not take the MEAN.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

    proc sql noprint;
    select sum(zindi&aa. * wprm&aa.)/sum(wprm&aa.) into :Mean_zindi_aa
    from Panel(where=(annee&ap.<="&nais18" ));
    quit;

Try this. Looks like you are trying to do a mean on (zindi&aa. * wprm&aa.). If you need the weighted average the above should work. because weighted average = sum(weight*variable)/sum(weights)

Upvotes: 3

Related Questions