SAS Data Gaps between purchases

[Question]

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

[Astounding · Accepted solution]

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?