topic Re: Dealing with date overlaps in SAS Programming

Dealing with date overlaps

div44 — Tue, 01 Aug 2017 19:43:56 GMT

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

Re: Dealing with date overlaps

Reeza — Tue, 01 Aug 2017 19:46:32 GMT

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

Re: Dealing with date overlaps

Astounding — Tue, 01 Aug 2017 20:42:36 GMT

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;

Re: Dealing with date overlaps

Kurt_Bremser — Wed, 02 Aug 2017 07:53:14 GMT

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.

Re: Dealing with date overlaps

Kurt_Bremser — Wed, 02 Aug 2017 07:57:24 GMT

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.