topic SAS Data Gaps between purchases in SAS Programming

SAS Data Gaps between purchases

Question — Tue, 11 Apr 2017 15:52:32 GMT

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

Re: SAS Data Gaps between purchases

Astounding — Tue, 11 Apr 2017 16:45:56 GMT

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?