topic Re: Generic Code to Create Date Intervals With Multiple Rows in SAS Programming

Generic Code to Create Date Intervals With Multiple Rows

brubin — Fri, 06 Jul 2018 13:49:30 GMT

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

Re: Generic Code to Create Date Intervals With Multiple Rows

RW9 — Fri, 06 Jul 2018 13:57:36 GMT

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;

Re: Generic Code to Create Date Intervals With Multiple Rows

brubin — Fri, 06 Jul 2018 14:09:36 GMT

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

Re: Generic Code to Create Date Intervals With Multiple Rows

Ksharp — Fri, 06 Jul 2018 14:36:58 GMT

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;