Flagging visit dates out of order

[bmerry1]

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.

[DBailey · Accepted solution]

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;

[bmerry1]

Thank you DBailey and Hai.kuo,

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

Best,

Brian

[ballardw]

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?

[bmerry1]

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.

[Haikuo]

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

[Astounding]

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.

[bmerry1]

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.

[SandyH]

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