## identify observations in rolling 12 month period

Solved
Regular Contributor
Posts: 182

# identify observations in rolling 12 month period

I'm working on a health utilization project.  I need to figure out how to identify patients using a specific algorithm for:

Patients with a specific (diagnosis) DX that has 2 outpatient vistis or more in any rolling 12 month period.   I can do it on a set period (e.g. 2014) but my study is over a 4 year period, so it should be any rolling 12 month period over the 4 years.

I have this code...it worked for my original data set, but I'm trying it again on a new table, but now I'm getting some patients with only one outpatient visit in the output.  I'm not sure where my error is...

proc sql;

create table OBS12 (drop=lo hi) as

select a.patssn, a.edi_pn, a.begdate as begdate_check, a.nobs as nobs_check, a.dx1 as dx1_check, a.dx2 as dx2_check, a.dx3 as dx3_check, a.dx4 as dx4_check,

b.begdate, b.nobs, b.dx1, b.dx2, b.dx3, b.dx4,

intnx('month',a.begdate,-12) as lo, intnx('month',a.begdate,12)as hi,

catx('',min(a.begdate,b.begdate),max(a.begdate,b.begdate))as minmax

from tni_encounterdate as a, tni_encounterdate as b

where a.patssn= b.patssn and b.begdate between calculated lo and calculated hi and a.nobs^= b.nobs

order patssn, begdate_check;

quit;

Proc sort data=tedni.tni_OBS12 out= tedni.tni_OBS12nd nodupkey;

by patssn begdate;

run;

Accepted Solutions
Solution
‎11-11-2015 12:36 PM
Regular Contributor
Posts: 161

## Re: identify observations in rolling 12 month period

[ Edited ]

I tried to simulate some data to test the code.... Hope this helps....

``````data have;
input patssn \$11. begdate:mmddyy10.;
cards;
123-45-6789 10/06/2015
123-45-6789 11/06/2014
123-45-6789 10/06/2014
223-45-6789 10/06/2015
423-45-6789 10/06/2015
423-45-6789 10/06/2015
523-45-6789 10/06/2014
523-45-6789 10/07/2013

;
RUN;

proc sql print;
select 	a.patssn ,a.begdate format=mmddyy10.,
(maxdate-365) as lo format=mmddyy10.
from 	have as a,
(select distinct patssn,max(distinct begdate) as maxdate from have group by patssn) b
where  a.patssn = b.patssn
and  a.begdate ge calculated lo
and  a.begdate ne maxdate;
quit;``````

Here is the output it produces.... (having 2 visits in rolling 365 days)

``````patssn	begdate	lo
123-45-6789	11/06/2014	10/06/2014
123-45-6789	10/06/2014	10/06/2014
523-45-6789	10/07/2013	10/06/2013``````

Good Luck...!!!

Kannan Deivasigamani

All Replies
Solution
‎11-11-2015 12:36 PM
Regular Contributor
Posts: 161

## Re: identify observations in rolling 12 month period

[ Edited ]

I tried to simulate some data to test the code.... Hope this helps....

``````data have;
input patssn \$11. begdate:mmddyy10.;
cards;
123-45-6789 10/06/2015
123-45-6789 11/06/2014
123-45-6789 10/06/2014
223-45-6789 10/06/2015
423-45-6789 10/06/2015
423-45-6789 10/06/2015
523-45-6789 10/06/2014
523-45-6789 10/07/2013

;
RUN;

proc sql print;
select 	a.patssn ,a.begdate format=mmddyy10.,
(maxdate-365) as lo format=mmddyy10.
from 	have as a,
(select distinct patssn,max(distinct begdate) as maxdate from have group by patssn) b
where  a.patssn = b.patssn
and  a.begdate ge calculated lo
and  a.begdate ne maxdate;
quit;``````

Here is the output it produces.... (having 2 visits in rolling 365 days)

``````patssn	begdate	lo
123-45-6789	11/06/2014	10/06/2014
123-45-6789	10/06/2014	10/06/2014
523-45-6789	10/07/2013	10/06/2013``````

Good Luck...!!!

Kannan Deivasigamani
Posts: 5,521

## Re: identify observations in rolling 12 month period

[ Edited ]

You might have some duplicate entries in your table (same visit with different nobs). Assuming that a patient cannot visit twice on the same day (or that you would count those as a single visit), this join condition would work better

``````where
a.patssn= b.patssn and
a.begdate < b.begdate and
intck("MONTH", a.begdate, b.begdate, "C") <= 12``````
PG
🔒 This topic is solved and locked.