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.
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;
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;
Thank you DBailey and Hai.kuo,
I'm trying both your options now. I really appreciate your help.
Best,
Brian
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?
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.
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
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.
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.
Visit 2 (if the dates are more different...) could be the one out of order, instead of 3 and 4?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.