DATA Step, Macro, Functions and more

SAS Data Gaps between purchases

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 96
Accepted Solution

SAS Data Gaps between purchases

Hi All,

 

I would like to retreive the period (Month/Year) where the gaps beteen purchases happen. The first table (Have) is what I have and the second tables (Want) is what I need.

 

Basically I would like to see when does the min_gap (1 week) , max_gap (4 weeks), mode_gap (1 week) occurrs.

 

Your help would be much appreciated. Please give me a shout if you have any questions.

 

Thank You

 

 

Have      
       
CUSTOMER_ID WEEK_START_DATE WEEK_END_DATE TRANS
50026 01-Jan-17 07-Jan-17 1
50026 08-Jan-17 14-Jan-17 1
50026 15-Jan-17 21-Jan-17 .
50026 22-Jan-17 28-Jan-17 .
50026 29-Jan-17 04-Feb-17 .
50026 05-Feb-17 11-Feb-17 .
50026 12-Feb-17 18-Feb-17 1
50026 19-Feb-17 25-Feb-17 .
50026 26-Feb-17 04-Mar-17 .
50026 05-Mar-17 11-Mar-17 2
50026 12-Mar-17 18-Mar-17 2
50026 19-Mar-17 25-Mar-17 .
50026 26-Mar-17 01-Apr-17 1
50027 02-Apr-17 08-Apr-17 .

 

Want      
       
Customer_id Month_Min_Gap Month_Max_Gap Month_Mode_Gap
50026 March & April Jan To Feb March & April

Accepted Solutions
Solution
‎04-12-2017 09:53 AM
Super User
Posts: 5,509

Re: SAS Data Gaps between purchases

You're trying to do too much at once.  Try constructing an intermediate data set, with one observation per gap.  For example, you might have CUSTOMER, GAP_START, and GAP_END which would contain 4 observations (based on your sample data):

 

50026  15-Jan-17  11-Feb-17

50026  19-Feb-17  04-Mar-17

50026  19-Mar-17  25-Mar-17

50027  02-Apr-17  08-Apr-17

 

Given that your data is sorted by CUSTOMER_ID, here is a way to get that result:

 

data want;

set have;

by customer_id trans notsorted;

if first.trans and trans=. then gap_start = week_start_date;

if last.trans and trans=. then do;

   gap_end = week_end_date;

   output;

end;

retain gap_start;

keep customer_id gap_start gap_end;

run;

 

With one observation per gap, you can run through all the computations you would like.  But consider some of the possible outcomes as well.  A customer might have a gap that spans 2 years, for example.  How would you report on that gap?

View solution in original post


All Replies
Solution
‎04-12-2017 09:53 AM
Super User
Posts: 5,509

Re: SAS Data Gaps between purchases

You're trying to do too much at once.  Try constructing an intermediate data set, with one observation per gap.  For example, you might have CUSTOMER, GAP_START, and GAP_END which would contain 4 observations (based on your sample data):

 

50026  15-Jan-17  11-Feb-17

50026  19-Feb-17  04-Mar-17

50026  19-Mar-17  25-Mar-17

50027  02-Apr-17  08-Apr-17

 

Given that your data is sorted by CUSTOMER_ID, here is a way to get that result:

 

data want;

set have;

by customer_id trans notsorted;

if first.trans and trans=. then gap_start = week_start_date;

if last.trans and trans=. then do;

   gap_end = week_end_date;

   output;

end;

retain gap_start;

keep customer_id gap_start gap_end;

run;

 

With one observation per gap, you can run through all the computations you would like.  But consider some of the possible outcomes as well.  A customer might have a gap that spans 2 years, for example.  How would you report on that gap?

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 104 views
  • 0 likes
  • 2 in conversation