DATA Step, Macro, Functions and more

Flagging visit dates out of order

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Flagging visit dates out of order

Hi and thank you in advance for any assistance you can provide.

I'm a bit of a SAS newbie and in running into a little bit of a difficulty.  What I would like to do is have a program that will allow me to either produce output that shows the instances of interest or flag the items of interest.  An example of the type of data I am looking at is:

ID               Visit               Date

101               1                  1-Jan-13

101               2                  3-Feb-13

101               3                  1-Apr-13

102               1                  2-Apr-13

102               2                  1-Jan-13

103               1                 10-Jan-13

103               2                  3-Mar-13

103               3                  2-Feb-13

What I would like to do is identify visits within subjects in which the dates are out of order.  For example I would like to either flag the items or produce output that would identify cases that fit the criteria.

For example:

ID 102 Visit 2 appears to have occurred before Visit 1

ID 103 Visit 3 appears to have occurred before Visit 2

Thank you again for your time and assistance.


Accepted Solutions
Solution
‎04-04-2013 12:53 PM
Super Contributor
Posts: 578

Re: Flagging visit dates out of order

data have;

input ID Visit VisitDate:anydtdte.;

format Visitdate date9.;

cards;

101               1                  1-Jan-13

101               2                  3-Feb-13

101               3                  1-Apr-13

102               1                  2-Apr-13

102               2                  1-Jan-13

103               1                 10-Jan-13

103               2                  3-Mar-13

103               3                  2-Feb-13

;

run;

proc sql;

create table want as

select

    t1.*

from

    have t1

    inner join have t2

        on t1.id=t2.id

            and t1.visit=t2.visit+1

where

    t2.VisitDate > t1.VisitDate;

quit;

View solution in original post


All Replies
Solution
‎04-04-2013 12:53 PM
Super Contributor
Posts: 578

Re: Flagging visit dates out of order

data have;

input ID Visit VisitDate:anydtdte.;

format Visitdate date9.;

cards;

101               1                  1-Jan-13

101               2                  3-Feb-13

101               3                  1-Apr-13

102               1                  2-Apr-13

102               2                  1-Jan-13

103               1                 10-Jan-13

103               2                  3-Mar-13

103               3                  2-Feb-13

;

run;

proc sql;

create table want as

select

    t1.*

from

    have t1

    inner join have t2

        on t1.id=t2.id

            and t1.visit=t2.visit+1

where

    t2.VisitDate > t1.VisitDate;

quit;

Occasional Contributor
Posts: 13

Re: Flagging visit dates out of order

Thank you DBailey and Hai.kuo,

I'm trying both your options now.  I really appreciate your help.

Best,

Brian

Super User
Posts: 11,343

Re: Flagging visit dates out of order

Do we have to worry about cases like:

ID    Visit   Date

1      1        1-Jan-13

1      2        5-Jan-13

1      3        2-Jan-13

1      4        3-Jan-13

Visit 4 is okay compared with vist 3 but still out of order compared with Visit 2?

Occasional Contributor
Posts: 13

Re: Flagging visit dates out of order

Hi Ballardw--good catch/point visit 4 would still be considered to be out of order as compared to visit 2.  For my purposes, it would be useful to flag both.

Thank you.

Respected Advisor
Posts: 3,156

Re: Flagging visit dates out of order

Or a data step approach (raw input stoled from 's post),

data have;

input ID Visit VisitDate:anydtdte.;

format Visitdate date9.;

cards;

101 1 1-Jan-13

101 2 3-Feb-13

101 3 1-Apr-13

102 1 2-Apr-13

102 2 1-Jan-13

103 1 10-Jan-13

103 2 3-Mar-13

103 3 2-Feb-13

;

data want;

  do until (last.id);

  set have;

by id;

ind=ifn(VisitDate<lag(VisitDate),1,ind);

  end;

  if ind then output;

  keep id;

run;

Haikuo

Super User
Posts: 5,516

Re: Flagging visit dates out of order

If you really need to catch the cases that ballardw pointed out, here's another approach.  It assumes you have already sorted the data by ID VISIT.

data want;

   set have;

   by id;

   if first.id then do;

      earlier_visit=visit;

      earlier_date=date;

      retain earlier_visit earlier_date;

   end;

   else do;

      if date > earlier_date then do;

         earlier_visit=visit;

         earlier_date=date;

      end;

      else output;

   end;

run;

Good luck.

Occasional Contributor
Posts: 13

Re: Flagging visit dates out of order

Posted in reply to Astounding

Thank you for your time as well Astounding.  As I was testing the programs DBailey's approach might be enough although I do like the additional information provided by your approach.

Thank you again.

Occasional Contributor
Posts: 8

Re: Flagging visit dates out of order

Posted in reply to Astounding

Visit 2 (if the dates are more different...) could be the one out of order, instead of 3 and 4?

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 255 views
  • 3 likes
  • 6 in conversation