SAS differences in outcome between sql and proc means

Question

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

• what I am missing with the proc sql so that it does give an absurd result ?
• is there a better way to obtain my macro variable &mean_zindi_aa

Answer 1

    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)

Answer 2

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.