- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you DBailey and Hai.kuo,
I'm trying both your options now. I really appreciate your help.
Best,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Visit 2 (if the dates are more different...) could be the one out of order, instead of 3 and 4?