DATA Step, Macro, Functions and more

Dealing with date overlaps

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Dealing with date overlaps

Hello,

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

 

Thank you

 

IDstart_dateend_datekeep
    
12-Jul-175-Jul-171
14-Jul-1715-Jul-170
111-Jul-1720-Jul-171
117-Jul-1722-Jul-170
119-Jul-1724-Jul-170
121-Jul-1723-Jul-171

Accepted Solutions
Solution
‎08-02-2017 12:00 PM
Super User
Posts: 5,516

Re: Dealing with date overlaps

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;

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Dealing with date overlaps

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

Solution
‎08-02-2017 12:00 PM
Super User
Posts: 5,516

Re: Dealing with date overlaps

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;

Super User
Posts: 7,851

Re: Dealing with date overlaps

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,851

Re: Dealing with date overlaps

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 107 views
  • 0 likes
  • 4 in conversation