topic Re: How do I collapse date periods, gived some conditions in SAS Programming

How do I collapse date periods, gived some conditions

terjeph — Thu, 11 May 2023 01:04:02 GMT

I want to combine two or more time periods into one, contingent upon a) same ID, b) same value on another variable (hours) and gaps between time periode. If there is a gap between two periods of more than two days, then this should still be two periods (cf. ID2, first and second row):

Data have:

ID Startdate Enddate Hours

1 20230101 20230108 8

1 20230109 20230131 8

1 20230201 20230228 4

2 20230101 20230108 8

2 20230112 20230131 8

2 20230201 20230228 4

Data want:

ID Startdate Enddate Hours

1 20230101 20230131 8

1 20230201 20230228 4

2 20230101 20230108 8

2 20230112 20230131 8

2 20230201 20230228 4

Re: How do I collapse date periods, gived some conditions

ballardw — Thu, 11 May 2023 02:05:35 GMT

Before starting, are your start and end date values actually SAS date values, i.e. numeric type with a format like (apparently) yymmddn8.?

Re: How do I collapse date periods, gived some conditions

terjeph — Thu, 11 May 2023 02:11:09 GMT

Yes, they are - yymmddn8.

Re: How do I collapse date periods, gived some conditions

whymath — Thu, 11 May 2023 02:15:00 GMT

You can use a lag() function to compute the gap between last row and current row.

data have;
  informat ID $8. Startdate Enddate b8601da8.;
  format Startdate Enddate b8601da8.;
  input ID $ Startdate Enddate Hours;
  cards;
1 20230101 20230108 8
1 20230109 20230131 8
1 20230201 20230228 4
2 20230101 20230108 8
2 20230112 20230131 8
2 20230201 20230228 4
;
run;

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 do I collapse date periods, gived some conditions

terjeph — Thu, 11 May 2023 02:57:34 GMT

This works fine. Let's say we don't have the hours variable. How would that change the code?

Re: How do I collapse date periods, gived some conditions

user40 — Thu, 11 May 2023 09:01:47 GMT

Hello

Did do not work. I cannot remove the overlap row in this solution. How can I combined row one and two in this example? this need should only be from 1. januar 2023 until 31. january 2023 with 8 hours service.

But if you run this code it would just delete last row.

Can anyone check this?

Re: How do I collapse date periods, gived some conditions

user40 — Thu, 11 May 2023 09:13:27 GMT

The solution below is not correct, so there we only delete the row that are extended in regard that sum the duration of ID 1. Can someone explain what the correct solution is?