Hi everyone.
I was wondering if it is possible to write a code that combines date intervals with multiple rows. For example I would like this:
ID start_date end_date
A 1/1/17 1/10/17
A 1/11/17 1/14/17
A 1/20/17 1/22/17
B 1/4/17 1/8/17
To output:
ID start_date end_date
A 1/1/17 1/14/17
A 1/20/17 1/22/17
B 1/4/17 1/8/17
data have;
input ID $ start_date : mmddyy10. end_date : mmddyy10.;
format start_date end_date mmddyy10.;
cards;
A 1/1/17 1/10/17
A 1/11/17 1/14/17
A 1/20/17 1/22/17
B 1/4/17 1/8/17
;
run;
data temp;
set have;
by id;
if first.id or start_date ne lag(end_date)+1 then group+1;
run;
data want;
set temp(rename=(start_date=_start_date));
by group;
retain start_date;
if first.group then start_date=_start_date;
if last.group;
format start_date mmddyy10.;
drop _start_date;
run;
The answer is yes. Just control the output statement and output and retain the dates, e.g. (not tested as no test data in the form of a datastep):
data want; set have; retain start end; by id; if first.id then start=start_date; else if start_date ne lag(end_date)+1 then do; end_date=lag(end_date); output; start=start_date end; run;
data have;
input ID $ start_date : mmddyy10. end_date : mmddyy10.;
format start_date end_date mmddyy10.;
cards;
A 1/1/17 1/10/17
A 1/11/17 1/14/17
A 1/20/17 1/22/17
B 1/4/17 1/8/17
;
run;
data temp;
set have;
by id;
if first.id or start_date ne lag(end_date)+1 then group+1;
run;
data want;
set temp(rename=(start_date=_start_date));
by group;
retain start_date;
if first.group then start_date=_start_date;
if last.group;
format start_date mmddyy10.;
drop _start_date;
run;
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.