topic Re: Split date range into one row per day in SAS Data Management

Split date range into one row per day

davies — Tue, 27 Nov 2018 16:06:53 GMT

Hi there,

I've seen posts about splitting date ranges into months, but can't quite figure out how to split a date range into one row per day. For example, I have data that looks like this:

ID StartDate EndDate Status

1 Jan 28, 2018 Jan 30, 2018 1

1 Jan 30, 2018 Feb 2, 2018 2

What I need is this:

ID Date Status

1 Jan 28, 2018 1

1 Jan 29, 2018 1

1 Jan 30, 2018 2

1 Jan 31, 2018 2

1 Feb 1, 2018 2

1 Feb 2, 2018 2

Re: Split date range into one row per day

SuryaKiran — Tue, 27 Nov 2018 16:18:09 GMT

Hi,

Welcome to SAS Community world.

Are your date values numeric or character? First you may need to convert them to actual date values if they are in character.

data have;
input id start :date9. enddate :date9. status;
datalines;
1 28JAN2018 30JAN2018 1
1 30JAN2018 02FEB2018 2
;
run;

data want(keep=id date status);
set have;
do i=start to enddate;
date=i;
output;
end;
format date date9.;
run;

Re: Split date range into one row per day

ballardw — Tue, 27 Nov 2018 16:18:05 GMT

If the values of your StartDate and EndDate are SAS date values the approach is relatively easy, though the disappearance of Jan 30, 2018 with status 1 will need some explanation as to the rules involved.

I am using mmddyy10 date layout values as the worddate format is uglier to read with datalines.

data have;
   input ID     StartDate :mmddyy10.          EndDate :mmddyy10.    Status;
   format startdate enddate mmddyy10.;
datalines;
1   01/28/2018    01/30/2018    1
1   01/30/2018    02/02/2018    2
;
run;

data want;
   set have;
   do date = startdate to enddate;
      output;
   end;
   drop startdate enddate;
   format date date9.;
run;

Your "have" data would require a processing step with rules to change the enddate fo 01/30/2018 to 01/29/2018 to use this approach but this is demonstrating how to turn the intervals into one observation per date in the interval.

Re: Split date range into one row per day

novinosrin — Tue, 27 Nov 2018 16:18:53 GMT

Are your dates SAS numeric dates or char?

Re: Split date range into one row per day

davies — Tue, 27 Nov 2018 16:26:05 GMT

Hi,

Thanks all for the quick replies! As luck would have it, just after I posted this I adapted this code to account for dates: https://communities.sas.com/t5/Statistical-Procedures/How-to-split-record-into-multiple-rows-one-row-per-month-year/td-p/249306

In the end, I did this, but your way seems much more efficient, I'll try that!

Thanks again!

data want;
  set have;
  date=StartDate;
  if StartDate lt EndDate then do while (date lt EndDate);
   day=day(date);
   month=month(date);
   year=year(date);
   output;
  date=intnx('day',date,1);
  end;
  drop date;
run;

Re: Split date range into one row per day

ballardw — Tue, 27 Nov 2018 16:36:46 GMT

@davies wrote:

Hi,

Thanks all for the quick replies! As luck would have it, just after I posted this I adapted this code to account for dates: https://communities.sas.com/t5/Statistical-Procedures/How-to-split-record-into-multiple-rows-one-row-per-month-year/td-p/249306

In the end, I did this, but your way seems much more efficient, I'll try that!

Thanks again!

data want;
  set have;
  date=StartDate;
  if StartDate lt EndDate then do while (date lt EndDate);
   day=day(date);
   month=month(date);
   year=year(date);
   output;
  date=intnx('day',date,1);
  end;
  drop date;
run;

Behavior difference at beginning of the interval if the start = end. With the DO loop the it will execute once. The DO WHILE as shown would not execute.

I would guess the above code was written by someone either less familiar with SAS DO loop construct using start and end variable values or from a programming language/school that does everything with DO WHILE/UNTIL and not iterated loops for some other reason..