BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bmerry1
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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

8 REPLIES 8
DBailey
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thank you DBailey and Hai.kuo,

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

Best,

Brian

ballardw
Super User

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
Calcite | Level 5

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
Onyx | Level 15

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
PROC Star

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
Calcite | Level 5

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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