Split date range into one row per day

[davies]

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

[SuryaKiran]

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;

[ballardw]

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.

[novinosrin]

Are your dates SAS numeric dates or char? 

[davies]

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...

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;

[ballardw]

---

@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...

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..