## How to calculate gaps beteween dates , long dataset

Solved
Frequent Contributor
Posts: 96

# 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: 6,785

## 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;

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

## 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
• 171 views
• 0 likes
• 2 in conversation