topic How to collapse time intervals contingent upon some arguments in SAS Programming

How to collapse time intervals contingent upon some arguments

terjeph — Tue, 26 Sep 2023 19:40:03 GMT

I would like to combine two or more consecutive time periods into one, based on the condition that they have the same ID and the same value for another variable (hours). A new record in the output data will be generated if there is a new ID or a change in the value of hours within an ID. Additionally, if there is a break or a gap between the Enddate of one period and the Startdate of the next period within an ID, a new record will also be created in the output data.

Data Have:

Rec ID Startdate Enddate Hours
1 1 01/01/2023 01/08/2023 8
2 1 01/09/2023 01/31/2023 8
3 1 02/01/2023 02/28/2023 4
4 1 03/01/2023 05/31/2023 8
5 1 06/01/2023 09/26/2023 8
6 2 01/01/2023 01/07/2023 8
7 2 01/10/2023 01/31/2023 8
8 2 02/01/2023 02/28/2023 4

Data Want:

ID Startdate Enddate Hours
1 01/01/2023 31/01/2023 8
1 02/01/2023 02/28/2023 4
1 03/01/2023 09/26/2023 8
2 01/01/2023 01/07/2023 8
2 01/10/2023 01/31/2023 8
2 02/01/2023 02/28/2023 4

Based on a former discussion in SAS Communities I’m working on this code which doesn’t fully make it (for example, it doesn’t collapse record 4 and 5):

proc sort data=have out=have2; by id hours startdate; run; data want; set have2; by id hours; lag_enddate=lag(enddate); if not first.hours then do; if startdate-lag_enddate<=2 then delete_flag=1; end; if delete_flag then delete; drop lag_enddate delete_flag; run;

Re: How to collapse time intervals contingent upon some arguments

ballardw — Tue, 26 Sep 2023 19:36:29 GMT

Are you really sure that these are supposed to "collapse"? The 5/31/2023 date is not included. Your rule actually states that if there is a gap they should not be collapsed but you ask specifically about records 4 and 5 that should.

4 1 03/01/2023 05/30/2023 8
5 1 06/01/2023 09/26/2023 8

Re: How to collapse time intervals contingent upon some arguments

terjeph — Tue, 26 Sep 2023 19:39:19 GMT

It should be 05/31/2023 - sorry

Re: How to collapse time intervals contingent upon some arguments

mkeintz — Tue, 26 Sep 2023 23:01:07 GMT

Assuming the data are sorted by id/startdate, then (untested in the absence of sample data in the form of a working DATA step):

data want (drop=_: nxt_:);
  set have;
  by id hours notsorted;
  merge have have (firstobs=2 keep=startdate rename=(startdate=nxt_startdate));

  retain _startdate;
  if first.hours or lag(enddate)<startdate-1 then _startdate=startdate;

  if last.hours or nxt_startdate > enddate+1;
  startdate=_startdate;
run;

Re: How to collapse time intervals contingent upon some arguments

terjeph — Wed, 27 Sep 2023 11:05:20 GMT

Worked well! Thanks!