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 |
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?
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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.