BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
terjeph
Obsidian | Level 7

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

4 REPLIES 4
ballardw
Super User

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

 

terjeph
Obsidian | Level 7
It should be 05/31/2023 - sorry
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
terjeph
Obsidian | Level 7

Worked well! Thanks!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 436 views
  • 0 likes
  • 3 in conversation