topic Re: Flagging visit dates out of order in SAS Programming

Flagging visit dates out of order

bmerry1 — Thu, 04 Apr 2013 16:31:42 GMT

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.

Re: Flagging visit dates out of order

DBailey — Thu, 04 Apr 2013 16:53:55 GMT

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;

Re: Flagging visit dates out of order

ballardw — Thu, 04 Apr 2013 17:00:34 GMT

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?

Re: Flagging visit dates out of order

Haikuo — Thu, 04 Apr 2013 17:00:53 GMT

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

Re: Flagging visit dates out of order

bmerry1 — Thu, 04 Apr 2013 18:19:14 GMT

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.

Re: Flagging visit dates out of order

bmerry1 — Thu, 04 Apr 2013 18:20:17 GMT

Thank you DBailey and Hai.kuo,

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

Best,

Brian

Re: Flagging visit dates out of order

Astounding — Thu, 04 Apr 2013 20:24:36 GMT

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.

Re: Flagging visit dates out of order

bmerry1 — Fri, 05 Apr 2013 14:22:03 GMT

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.

Re: Flagging visit dates out of order

SandyH — Mon, 08 Apr 2013 02:54:53 GMT

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