DATA Step, Macro, Functions and more

How to calculate gaps beteween dates , long dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

How to calculate gaps beteween dates , long dataset

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

Accepted Solutions
Solution
‎05-11-2017 08:16 AM
Super User
Posts: 5,509

Re: How to calculate gaps beteween dates , long dataset

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;

View solution in original post


All Replies
Solution
‎05-11-2017 08:16 AM
Super User
Posts: 5,509

Re: How to calculate gaps beteween dates , long dataset

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;

Frequent Contributor
Posts: 96

Re: How to calculate gaps beteween dates , long dataset

Posted in reply to Astounding
Thank You Astounding!
It's working :-)

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Frequent Contributor
Posts: 96

Re: How to calculate gaps beteween dates , long dataset

Posted in reply to Astounding
Thank You Astounding!
It's working :-)

##- Please type your reply above this line. Simple formatting, no
attachments. -##
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 122 views
  • 0 likes
  • 2 in conversation