## do weighted-average across multiple columns

Solved
Super Contributor
Posts: 409

# do weighted-average across multiple columns

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.

Accepted Solutions
Solution
‎04-18-2012 11:01 AM
Regular Contributor
Posts: 151

## Re: do weighted-average across multiple columns

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.

All Replies
Solution
‎04-18-2012 11:01 AM
Regular Contributor
Posts: 151

## Re: do weighted-average across multiple columns

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.

Super Contributor
Posts: 409

## Re: do weighted-average across multiple columns

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

Super Contributor
Posts: 409

## Re: do weighted-average across multiple columns

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

PROC Star
Posts: 8,164

## Re: do weighted-average across multiple columns

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.

Super Contributor
Posts: 409

## Re: do weighted-average across multiple columns

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

🔒 This topic is solved and locked.