DATA Step, Macro, Functions and more

Average of last 4 weeks in a row wise

Reply
Frequent Contributor
Posts: 84

Average of last 4 weeks in a row wise

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:

DateCust_NumAmountDaysIndexLast 4 weeks AmountLast 4 weeks DaysLast 4 weeks Index
02-03-2015ABC1507240053
02-03-2015DEF1505340063

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.

Super User
Posts: 17,829

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;

Ask a Question
Discussion stats
  • 1 reply
  • 214 views
  • 0 likes
  • 2 in conversation