topic Creating weighted average in SAS Procedures

Creating weighted average

deleted_user — Tue, 15 Jun 2010 14:41:23 GMT

Hello

I am trying to create the functionality of weighted average using SAS and need some help with the code.

I have the following variables in my dataset arranged in the following order.

Product - (Consumer)
Rewards code - (Y or N)
Risk type - (R1 to R5, Novalue)
Relationship -(Full, medium, low)
Number of accts
dollarprofit

For Risktype = Novalue and Relationship type = Full

I am trying to compute the weighted average as
=(Dollerprofit(of R1-Full)*numberofaccts(of R1-Full)+
Dollerprofit(of R2-Full)*numberofaccts(of R2-Full)+
Dollerprofit(of R3-Full)*numberofaccts(of R3-Full)+
Dollerprofit(of R4-Full)*numberofaccts(of R4-Full)+
Dollerprofit(of R5-Full)*numberofaccts(of R5-Full))/
sum[numberofaccts(of R1-Full)+ numberofaccts(of R2-Full)+
numberofaccts(of R3-Full)+ numberofaccts(of R4-Full)+
numberofaccts(of R5-Full))

The similar needs to be achieved for
Risktype = Novalue and Relationship type = medium
Risktype = Novalue and Relationship type = low

and needs to be done for all Rewards = Yes and No.

Could anyone tell me how to achieve this using SAS

Thanks

Re: Creating weighted average

SASKiwi — Wed, 16 Jun 2010 01:38:12 GMT

I suggest you investigate the MEANS procedure. The combination of the MEAN statistic with the WEIGHT statement will do a weighted average. If I understand your description correctly it appears you are weighting by the number of accounts.

Your skeleton code will probably look like something like this:

proc means data = xxx nway;
where risk type in R1 to R5;
class relationship;
var dollarprofit;
weight Number of accts;
output out = xxx_out
mean =
;
run;

You can also do weighted averages in the REPORT procedure but the learning curve is much higher.

Re: Creating weighted average

deleted_user — Wed, 16 Jun 2010 18:44:58 GMT

Thank you this works. Appreciate it.