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?
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.
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.