Generic Code to Create Date Intervals With Multiple Rows

[brubin]

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

[RW9]

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;

[brubin]

Would you mind putting it in the terms of the variables:
unique_ID1 service_from_date1 service_to_date1

[Ksharp · Accepted solution]

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;