do weighted-average across multiple columns

[podarum]

Hi, I'm not sure if this is possible in SAS, (or what am I saying, ofcoarse it's possible in SAS).  I'm looking to do a weighted average for multiple columns.  So If I have about 20 columns (eg. COL1 -- COL 20) and another 4 columns of weighted Counts (Cnts).  I want the first 4 columns of the 20 have a weighted average count from each of the corresponding column of the 5 Counts.

HAVE:

STORE     Sep11_A   Oct11_A   Nov11_A   Dec11_A....   Sep11_E   Oct11_L  Nov11_L  Dec11_L Sep11_cnts  Oct11_cnts  nov11_cnts Dec11_cnts  

Store1                1              5                6                4               2                  3               2            6               2                      8                    10                 12                                      

Store2                8              5                2                2               6                  5               3            4                6                      4                     11                 7                

Store3  ...and so on....      

I would like to get each Sep11 columns (from A to E) to be weighted by the Sep11_cnts  and same for all the other months..

The final would look like this:

             Sep11_A      Oct11_A    Nov11_A     Dec11_A .... Sep11_E    Oct11_L   Nov11_L    Dec11_L

ALL            6.25              5              3.9                   3.26             5             3.66           2.52           5.26

I can do this by one which is time consuming.

Proc Means data=HAVE NOPRINT;

output out=WANT (drop=_TYPE_ _FREQ_) MEAN=;

var Dec11_Price;

weight Dec11_Counts;

Run;

Also can someone please tell me how I can insert code as a copy and past straight from SAS without going to the HTML button above?  Thanks.

[Keith · Accepted solution]

You can specify WEIGHT as an option in the VAR statement, so all you need to do is specify multiple VAR statements with a different WEIGHT variable for each.

e.g.

Proc Means data=HAVE NOPRINT;

output out=WANT (drop=_TYPE_ _FREQ_) MEAN=;

var Sep11_A Sep11_B Sep11_C Sep11_D Sep11_E / weight=Sep11_cnts;

var Oct11_A Oct11_B Oct11_C Oct11_D Oct11_E / weight=Oct11_cnts;

var Nov11_A Nov11_B Nov11_C Nov11_D Nov11_E / weight=Nov11_cnts;

var Dec11_A Dec11_B Dec11_C Dec11_D Dec11_E / weight=Dec11_cnts;

Run;

Hopefully I have understood your question correctly.  With regards to your 2nd question on copying and pasting SAS code, my method is to paste it into Word first and then copy and paste from there.

[podarum]

Thanks Keith, I'll give it a try..

[podarum]

Thanks Keith, works perfectly.  How do I now mark a reply as the Right Answer?

[art297]

If you are asking what I think you are asking, at the lower portion of the box that surrounds each response there are two icons: one that indicates "correct answer" and one that indicates "helpful answer".

If you click on "correct answer" that person is awarded four points for having provided the "right" answer.

[podarum]

Thanks Art, I see it now, I didn't earlier thoug (I was logged in)...