## Average of last 4 weeks in a row wise

Frequent Contributor
Posts: 84

# Average of last 4 weeks in a row wise

Hi ,

I have last 5 weeks customers data, i want to compare recent week data to last 4 weeks data. The output data set has only one row for each customer and their last 4 weeks metrics.

The data looks like:

Data Have;

input Date mmddyy10. Cust_Num\$ Amount Days Index ;

cards;

02-03-2015 ABC 150 7 2

01-27-2015 ABC 100 5 2

01-20-2015 ABC 100 5 2

01-13-2015 ABC 100 5 2

01-06-2015 ABC 100 5 2

02-03-2015 DEF 150 5 3

01-27-2015 DEF 150 6 3

01-20-2015 DEF 150 6 3

01-06-2015 DEF 100 6 3

run;

I want Output dataset Like this:

 Date Cust_Num Amount Days Index Last 4 weeks Amount Last 4 weeks Days Last 4 weeks Index 02-03-2015 ABC 150 7 2 400 5 3 02-03-2015 DEF 150 5 3 400 6 3

Actually i want to compare recent date with last 4 wees data. Here feb 2nd is the recent date for ABC customer it has 4 weeks data So it will take 4 weeks and DEF has only 3 weeks data it will take 3 weeks data only.

Last 4 wees amount= Sum of last weeks amount

Last 4 weeks Days= SumProcduct(Amount,Days)/Last 4 weeks amount

Last 4 weeks Index=Sumproduct(Amount,Days)/Last 4 weeks amount

Sumproduct will actually multiplies the both columns in Excel. I want to multiply amount and days then devide that amount with the total 4 wees amount because i want weighted days same for index.

Super User
Posts: 23,720

## Re: Average of last 4 weeks in a row wise

Your logic for last 4 weeks index appears incorrect or exactly the same as days.

Here's one way to do it, in a single data step. A bit cumbersome but straightforward enough:

Data Have;

input Date mmddyy10. Cust_Num\$ Amount Days Index ;

cards;

02-03-2015 ABC 150 7 2

01-27-2015 ABC 100 5 2

01-20-2015 ABC 100 5 2

01-13-2015 ABC 100 5 2

01-06-2015 ABC 100 5 2

02-03-2015 DEF 150 5 3

01-27-2015 DEF 150 6 3

01-20-2015 DEF 150 6 3

01-06-2015 DEF 100 6 3

run;

proc sort data=have;

by cust_num descending date;

run;

data want;

set have;

by cust_num descending date;

retain date_out amount_out days_out index_out amount_prev days_prev index_prev running_days running_index;

if first.cust_num then do;

call missing (date_out, amount_out, days_out, index_out,

amount_prev, days_prev, index_prev, running_days, running_index);

date_out=date;

amount_out=amount;

days_out=days;

index_out=index;

end;

else do;

amount_prev=sum(amount_prev, amount);

running_days=sum(amount*days,running_days);

running_index=sum(index*days, running_index);

end;

if last.cust_num then do;

days_prev=running_days/amount_prev;

index_prev=running_index/amount_prev;

output;

end;

drop running: date amount days index;

run;

Discussion stats