Average of last 4 weeks in a row wise

[kumarK]

Hi ,

I have faced this problem please help me on this problem .

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.

Thanks in advance.

[Reeza]

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;