DATA Step, Macro, Functions and more

Generic Code to Create Date Intervals With Multiple Rows

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Generic Code to Create Date Intervals With Multiple Rows

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


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 10,788

Re: Generic Code to Create Date Intervals With Multiple Rows

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,617

Re: Generic Code to Create Date Intervals With Multiple Rows

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;
New Contributor
Posts: 2

Re: Generic Code to Create Date Intervals With Multiple Rows

Would you mind putting it in the terms of the variables:
unique_ID1 service_from_date1 service_to_date1
Solution
2 weeks ago
Super User
Posts: 10,788

Re: Generic Code to Create Date Intervals With Multiple Rows

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 86 views
  • 0 likes
  • 3 in conversation