0

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
Joe
  • 62,789
  • 6
  • 49
  • 67
Anthony Martin
  • 767
  • 1
  • 9
  • 28
  • 1
    A tip for future reference.. you can help yourself debug issues like this simply by breaking out the formula into different variables, and working on a limited number of observations where it is possible to calculate the answer by hand. You would have picked up this issue pretty quickly that way. – Robert Penridge Jun 25 '15 at 17:35
  • yes I am kind of embarassed. I am just starting to know how to use sql, so I wrongly assumed it was a problem of the way I used it instead of checking the formula – Anthony Martin Jun 25 '15 at 20:15

2 Answers2

3
    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)

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
1

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.

DomPazz
  • 12,415
  • 17
  • 23