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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.