topic Re: Remove overlapping time periods in New SAS User

Remove overlapping time periods

NewUsrStat — Sun, 09 Oct 2022 12:53:26 GMT

Hi guys,

suppose to have the following:

data DB1; input ID :$20. (Start End)(:date9.) Absence_reason :$20.; format Start End date9.; cards; 0001 01JAN2015 06FEB2015 vacation 0001 02JAN2015 02JAN2015 vacation 0001 13APR2015 31DEC2015 sick leave 0002 01JAN2017 12JUL2017 vacation 0002 12JUN2017 18JUN2017 vacation ...;

I would like to remove records having the same (!) "absence reason" when the period falls into a larger one. For example for 0001, the row 02JAN2015 - 02JAN2015 should be removed because already in 01JAN2015 - 06FEB2015. The same for 0002 relative to 12JUN2017 - 18JUN2017. It is clearly an error in the file I received to perform the analysis.

Desired output:

data DB1; input ID :$20. (Start End)(:date9.) Absence_reason :$20.; format Start End date9.; cards; 0001 01JAN2015 06FEB2015 vacation 0001 13APR2015 31DEC2015 sick leave 0002 01JAN2017 12JUL2017 vacation ...;

Re: Remove overlapping time periods

PaigeMiller — Sun, 09 Oct 2022 13:42:05 GMT

Is the problem always that there are two consecutive lines and one needs to be removed? Could there ever be three consecutive or non-consecutive lines where some need to be removed?

I don't understand the result for ID 0002, they two records overlap, why are they not combined?

Re: Remove overlapping time periods

NewUsrStat — Sun, 09 Oct 2022 13:42:52 GMT

I don't understand your questions. Data periods should not be combined, but only removed when they are sub periods of a greater one(s) by the same absence reason. The records relative to 0002 overlap and this is the reason why I would like to remove the shortest one. Unfortunately it is a mistake made by the person that annotated the absence for the worker 0002.

Re: Remove overlapping time periods

PaigeMiller — Sun, 09 Oct 2022 13:43:48 GMT

Is it always two consecutive records where one needs to be removed? Could there ever be three records (consecutive or non-consecutive) where records need to be removed?

When there is partial overlap such as ID 0002, you want to keep the longest and remove the shortest?

Re: Remove overlapping time periods

NewUsrStat — Sun, 09 Oct 2022 17:38:11 GMT

So: "Could there ever be 3 records....?" Yes.
"When there is partial overlap....?" Yes, the longest is the informative one I would like to retain.

Re: Remove overlapping time periods

Ksharp — Mon, 10 Oct 2022 11:39:17 GMT

data DB1;
input ID :$20. (Start End)(:date9.) Absence_reason $20.;
format Start End date9.;
cards;
0001 01JAN2015 06FEB2015 vacation 
0001 02JAN2015 02JAN2015 vacation 
0001 13APR2015 31DEC2015 sick leave 
0002 01JAN2017 12JUL2017 vacation 
0002 12JUN2017 18JUN2017 vacation 
;
data temp;
 set db1;
do date=start to end;
 output;
end;
drop start end;
format date date9.;
run;
proc sort data=temp out=temp2 nodupkey;
by id date Absence_reason;
run;
data temp3;
 set temp2;
if id ne lag(id) or date ne lag(date)+1 or Absence_reason ne lag(Absence_reason) then group+1;
run;
proc sql;
create table want as
select group,max(id) as id,min(date) as start format=date9.,max(date) as end format=date9.,max(Absence_reason) as Absence_reason
 from temp3
  group by group;
quit;

Re: Remove overlapping time periods

NewUsrStat — Mon, 10 Oct 2022 14:36:26 GMT

Thank you very much! It works perfectly!