DATA Step, Macro, Functions and more

how to check date overlap: last visit's end date is later than this visit's start date

Accepted Solution Solved
Reply
Super Contributor
Posts: 318
Accepted Solution

how to check date overlap: last visit's end date is later than this visit's start date

For example, in the following data, for ID A002, visit 1's end date is 3/25/2016, but visit 2's start date is 3/24/2016.

How to identify such data issues? 

 

ID visitnum start_date end_date
A001 1 8/26/2015 9/3/2015
  2 9/17/2015 9/17/2017
  3 10/2/2015 10/2/2015
  4 10/30/2015 10/30/2015
  5 11/24/2015 11/24/2015
A002 1 3/17/2016 3/25/2016
  2 3/24/2016 3/24/2016
  3 3/31/2016 3/31/2016
  4 4/28/2016 4/28/2016
  5 5/25/2016 5/25/2016

nex


Accepted Solutions
Solution
‎01-25-2017 11:26 AM
PROC Star
Posts: 307

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to fengyuwuzu

One way is to use a retain to carry the previous end date to the next observation and then flag it. Something like the below:

 

data have;
    input ID $ visitnum	start_date :mmddyy10. end_date :mmddyy10.;
    datalines;
A001 1 8/26/2015 9/3/2015
A001 2 9/17/2015 9/17/2017
A001 3 10/2/2015 10/2/2015
A001 4 10/30/2015 10/30/2015
A001 5 11/24/2015 11/24/2015
A002 1 3/17/2016 3/25/2016
A002 2 3/24/2016 3/24/2016
A002 3 3/31/2016 3/31/2016
A002 4 4/28/2016 4/28/2016
A002 5 5/25/2016 5/25/2016
;

data want;  
    retain _lst;
    set have;
    by id visitnum;
    if first.id then do;
        flag = 0;
        _lst = end_date;
    end;
    else do;
        flag = (_lst > end_date);
        _lst = end_date;
    end;
    format start_date end_date mmddyy10.;
run;

View solution in original post


All Replies
Solution
‎01-25-2017 11:26 AM
PROC Star
Posts: 307

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to fengyuwuzu

One way is to use a retain to carry the previous end date to the next observation and then flag it. Something like the below:

 

data have;
    input ID $ visitnum	start_date :mmddyy10. end_date :mmddyy10.;
    datalines;
A001 1 8/26/2015 9/3/2015
A001 2 9/17/2015 9/17/2017
A001 3 10/2/2015 10/2/2015
A001 4 10/30/2015 10/30/2015
A001 5 11/24/2015 11/24/2015
A002 1 3/17/2016 3/25/2016
A002 2 3/24/2016 3/24/2016
A002 3 3/31/2016 3/31/2016
A002 4 4/28/2016 4/28/2016
A002 5 5/25/2016 5/25/2016
;

data want;  
    retain _lst;
    set have;
    by id visitnum;
    if first.id then do;
        flag = 0;
        _lst = end_date;
    end;
    else do;
        flag = (_lst > end_date);
        _lst = end_date;
    end;
    format start_date end_date mmddyy10.;
run;
Super Contributor
Posts: 318

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to collinelliot

Thanks, Collinelliot. 

But somehow there is a wrong flag when I ran your code. see red arrow below. 

 

Capture.PNG

PROC Star
Posts: 307

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to fengyuwuzu

The previous date is in 2017, which is later than the next date, so the flag seems correct. Unless I don't understand what you want.

Super Contributor
Posts: 318

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to collinelliot

you are right. It was a typo (2017 should be 2015). Your code works! Thank you

PROC Star
Posts: 307

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to fengyuwuzu

You're welcome!

Super User
Posts: 11,343

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to fengyuwuzu

First would be ensure that the values are SAS date values and not character. Is that the case?

Super Contributor
Posts: 318

Re: how to check date overlap: last visit's end date is later than this visit's start date

My dates are in yyyy-mm-dd format in the real SAS dateset, which can be compared differectly either in numeric format or character format. I did not pay attention when typing the example dates in excel.
Super User
Posts: 11,343

Re: how to check date overlap: last visit's end date is later than this visit's start date

Posted in reply to fengyuwuzu

fengyuwuzu wrote:
My dates are in yyyy-mm-dd format in the real SAS dateset, which can be compared differectly either in numeric format or character format. I did not pay attention when typing the example dates in excel.

So you show example data that is not in the form of the actual data?

And while comparisons of greater than or less than may work things like "by how much to they overlap" or any kind of actual difference cannot be done with either of those values but can be done with SAS date values.

Trusted Advisor
Posts: 1,018

Re: how to check date overlap: last visit's end date is later than this visit's start date

[ Edited ]
Posted in reply to fengyuwuzu

Both of these are untested.

 

If you only want to flag the second record of the overlap then a lag function and a by statemetn are the needed tools.

 

data want;  
    set have;
    by id ;
    if (first.id=0 and start_date<lag(end_date)) then flag=1;
    else flag=0;
run;

 

 

It's a few more lines if you want to flag both the leading and trailing elements of the overlap.  In this case, flag will be the sequence of the record in a consecutive sequence of overlaps  (i.e. record K overlaps K+1 which overlaps K+3, yielding flag=1 then 2 then 3):

 

data want;
  set have (keep=id);
  by id;
  merge have
        have (firstobs=2 keep=start_date rename=(start_date=next_sd));
  if (first.id=0 and start_date<lag(end_date)) or 
      (end_date>next_sd and last.id=0) then flag+1;
  else flag=0;
run;

 

 

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 320 views
  • 3 likes
  • 4 in conversation