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 |
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;
Look at the LAG function to be able to access the previous observation values.
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;
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.
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.
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.
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.