topic Re: do weighted-average across multiple columns in SAS Procedures

do weighted-average across multiple columns

podarum — Wed, 18 Apr 2012 14:20:11 GMT

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.

Re: do weighted-average across multiple columns

Keith — Wed, 18 Apr 2012 15:01:09 GMT

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.

Re: do weighted-average across multiple columns

podarum — Wed, 18 Apr 2012 15:03:54 GMT

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

Re: do weighted-average across multiple columns

podarum — Thu, 19 Apr 2012 13:38:08 GMT

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

Re: do weighted-average across multiple columns

art297 — Thu, 19 Apr 2012 13:43:33 GMT

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.

Re: do weighted-average across multiple columns

podarum — Thu, 19 Apr 2012 13:45:32 GMT

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