How to calculate gaps beteween dates , long dataset

[Question]

Hi All,

I would like to create the fiewl week_return based on the tranasaction dates. For axmple this customer reatuns after 1 week, then after 2 weeks etc..Your help would be much appreciated.

Thank You

customer_id	WEEK_NO	Trans_date	weekly_latest_Sales	Week_return
50026	1	06-Jan-17	31,000,000
50026	2	13-Jan-17	53,000,000	1
50026	7	14-Feb-17	39,000,000	5
50026	10	10-Mar-17	64,000,000	3
50026	11	14-Mar-17	70,000,000	1
50026	13	31-Mar-17	49,000,000	2

[Astounding · Accepted solution]

It looks like you are using WEEK_NO to compute the difference (rather than TRANS_DATE) ... either way is perfectly fine.  As long as your data set is sorted by CUSTOMER_ID, you can use:

data want;

set have;

by customer_id;

week_return = dif(week_no);

if first.customer_id then week_return=.;

run;

[Question]

Thank You Astounding!
It's working 🙂

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Question]

Thank You Astounding!
It's working 🙂

##- Please type your reply above this line. Simple formatting, no
attachments. -##