identify observations in rolling 12 month period

[jenim514]

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;

[kannand · Accepted solution]

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

[PGStats]

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