Dealing with date overlaps

[div44]

Hello,

I want to keep only those observations that have no overlapping start_date and end_date time window.

Thank you

ID	start_date	end_date	keep
1	2-Jul-17	5-Jul-17	1
1	4-Jul-17	15-Jul-17	0
1	11-Jul-17	20-Jul-17	1
1	17-Jul-17	22-Jul-17	0
1	19-Jul-17	24-Jul-17	0
1	21-Jul-17	23-Jul-17	1

[Reeza]

Look at the LAG function to be able to access the previous observation values.

[Astounding · Accepted solution]

Since your dates don't look like any SAS date format that I know, I have to assume they are character strings.

I'm also assuming that you want each ID analyzed separately, and that your data set is already sorted by ID.

Finally, I'm also assuming that if there's an exact match (current start_date equals previous end_date) then KEEP should be 0.

That being said, here's one way:

data want;

set have;

by id;

prior_end = lag(end_date);

if first.id then keep=1;

else do;

   if input(start_date, date9.) <= input(prior_end, date9.) then keep=0;

   else keep=1;

end;

drop prior_end;

run;

If it turns out that you really do have SAS dates and not character strings, you can simplify one statement:

if start_date <= prior_end then keep=0;

[Kurt_Bremser]

The lag_function won't really cut it here, as one has to skip observations that have keep = 0 when "looking back", so we need a retained variable:

data have;
input
id start_date:date9. end_date:date9.;
format start_date end_date yymmddd10.;
cards;
1 2-Jul-17 5-Jul-17
1 4-Jul-17 15-Jul-17
1 11-Jul-17 20-Jul-17
1 17-Jul-17 22-Jul-17
1 19-Jul-17 24-Jul-17
1 21-Jul-17 23-Jul-17
;
run;

data want;
set have;
by id;
retain prior_end;
if first.id
then do;
  keep = 1;
  prior_end = end_date;
end;
else do;
  if start_date <= prior_end
  then keep = 0;
  else do;
    keep = 1;
    prior_end = end_date;
  end;
end;
drop prior_end;
run;

This results in the OP's required output dataset.

[Kurt_Bremser]

And please post your example data in the way you have been repeatedly shown by now (data step with cards/datalines). It saves us converting the posted tables into something usable. See it as courtesy to your helpers.