topic Re: identifying observations 12 month period in SAS Programming

identifying observations 12 month period

jenim514 — Tue, 27 Oct 2015 18:32:03 GMT

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.

Re: identifying observations 12 month period

Steelers_In_DC — Tue, 27 Oct 2015 19:04:01 GMT

If you have example data that will be helpful.

Re: identifying observations 12 month period

MikeZdeb — Wed, 28 Oct 2015 01:34:15 GMT

Hi, I think this works OK. Maybe you can adapt it to your data. It joins the data with itself and looks for dates with an ID that are with one year. I also did a hash solution and got the same results. Of course it is possible that I'm really zero-for-two, but I think it's OK.

Since original post ...

#1 fixed the INTNX function, should be 12 months not 1 year (forgot about that interval b oundary stuff)

#2 fixed the duplicate output (e.g. nobs 7 matched with nobs 10 duplicated with nobs 10 matched with nobs 7) using the variable MINMAX

* make some data, IDs 1 through 50, 1 to 5 observations per ID, random dates;

data x;
do id=1 to 50;
do j=1 to ceil(5*ranuni(99));
   date = '01jan2005'd + ceil(2000*ranuni(99));
   nobs+1;
   output;
end;
end;
keep id nobs date;
format date mmddyy10.;
run;

* join the data set with itself and look for dates within ID within one year;

proc sql;
create table join (drop=lo hi) as
select a.id, a.date as date_check, a.nobs as nobs_check, b.date, b.nobs,
intnx('month',a.date, -12) as lo, intnx('month',a.date,12) as hi,

catx(' ',min(a.nobs,b.nobs),max(a.nobs,b.nobs)) as minmax
from x as a, x as b
where a.id = b.id and b.date between calculated lo and calculated hi and a.nobs ^= b.nobs;
quit;

proc sort data=y out=y (drop=minmax) nodupkey;

by id minmax;

run;

Here's data set x, IDs 1 through 3 ...

Obs id date nobs

1     1    03/20/2005      1
2     1    03/12/2008      2
3     2    04/24/2006      3
4     2    04/02/2010      4
5     2    02/19/2009      5
6     2    06/29/2008      6
7     3    03/27/2008      7
8     3    08/15/2005      8
9     3    09/24/2009      9
10     3    09/01/2007     10
11     3    07/27/2008     11

Here's data set JOIN for the IDs ...

id=2

nobs_
Obs check date_check date nobs

1 5 02/19/2009 06/29/2008 6

id=3

nobs_
Obs check date_check date nobs

2      10     09/01/2007    07/27/2008     11
3       7     03/27/2008    09/01/2007     10
4       7     03/27/2008    07/27/2008     11

FYI ... the hash version with help from Better Hashing in SAS® 9.2 ...

https://support.sas.com/resources/papers/sgf2008/better-hashing-sas92.pdf

and the HTML screws up a statement, the &colon you see below should be a real colon as in :

data y (keep=id nobs date_check nobs_check date minmax);
retain id date nobs 0;
dcl hash h(dataset : 'x', multidata &colon;'y');
h.definekey('id');
h.definedata('date', 'nobs');
h.definedone();
do until(last);
set x end=last;
date_check = date;
nobs_check = nobs;
lo = intnx('month', date_check, -12);
hi = intnx('month', date_check, 12);
h.find();
minmax = catx(' ',min(nobs,nobs_check),max(nobs,nobs_check));
if date ge lo and date le hi and nobs ne nobs_check then output;
h.has_next(result: r);
do while(r ne 0);
   h.find_next();
   minmax = catx(' ',min(nobs,nobs_check),max(nobs,nobs_check));
   if date ge lo and date le hi and nobs ne nobs_check then output;
   h.has_next(result: r);
end;
end;
stop;
format date_check mmddyy10.;
run;

proc sort data=y out=y (drop=minmax) nodupkey;
by id minmax;
run;

Re: identifying observations 12 month period

jenim514 — Tue, 27 Oct 2015 21:23:10 GMT

This is sample data. The years of the whole data set is 2010-2014. So I need to identify sepcific Pat_ID with 2 or more ADMDATEs in a continuous 12 month period of time. For exampme if Pat_ID #1 had a DX1 or DX2 admitted once in Jan2012 and once in Mar2013...I'm do not want to count. If Pat_ID #2 had Dx1 or Dx2 admitted once in Dec 2013 and again in Jan2014, I do want to count. Thanks!

PAT_ID DX2 DX1 ADMDATE

1	49390 0	5409	05APR2014
2	2512	49392 0	19DEC2013
3	49390 0	72888	03JUN2014
3	49390 1	3094	26OCT2013
4	2752	49392 0	06OCT2013
5	49381 0	72252	26NOV2013
6	49320 0	7220	28APR2014
7	49390 0	55329	19DEC2013
8	49390 0	48882	08JUL2014
9	490	49392 0	08DEC2013

Re: identifying observations 12 month period

MikeZdeb — Wed, 28 Oct 2015 01:51:56 GMT

Hi. In your post you mentioned ...

" ... admitted once in Jan2012 and once in Mar2013...I'm do not want to count. If Pat_ID #2 had Dx1 or Dx2 admitted once in Dec 2013 and again in Jan2014 ... "

but there was no such data in the posted data. I added data to match that in your description and used the SQL method I posted earlier and the results seem to match your request...

* your data with my additions for ID 1 and ID 2;

data x;
input id (dx1 dx2) (:$5.) date :date9.;
format date mmddyy10.;
datalines;
1 49390 5409 01JAN2012
2 2512 49392 19DEC2013
3 49390 72888 03JUN2014
3 49390 3094 26OCT2013
4 2752 49392 06OCT2013
5 49381 72252 26NOV2013
6 49320 7220 28APR2014
7 49390 55329 19DEC2013
8 49390 48882 08JUL2014
9 490   49392 08DEC2013
1 999   99999 07MAR2013
2 888   88888 01JAN2014

;

* add observation number;

data x;
set x;
nobs = _n_;
run;

proc sql;
create table y (drop=lo hi) as
select a.id, a.date as date_check, a.nobs as nobs_check, a.dx1 as dx1_check, a.dx2 as dx2_check,
       b.date, b.nobs, b.dx1, b.dx2,
       intnx('month',a.date, -12) as lo, intnx('month',a.date,12) as hi,
       catx(' ',min(a.date,b.date),max(a.date,b.date)) as minmax
from x as a, x as b
where a.id = b.id and b.date between calculated lo and calculated hi and a.nobs ^= b.nobs

order id, date_check;
quit;

* get rid of duplicates;

proc sort data=y out=y (drop=minmax) nodupkey;
by id minmax;
run; hi mmddyy10.;
run;

RESULTS ...

id=2

nobs_ dx1_ dx2_
Obs check date_check check check nobs date dx1 dx2

1 2 12/19/2013 2512 49392 12 01/01/2014 888 88888

id=3

nobs_ dx1_ dx2_
Obs check date_check check check nobs date dx1 dx2

2 4 10/26/2013 49390 3094 3 06/03/2014 49390 72888

Re: identifying observations 12 month period

jenim514 — Wed, 28 Oct 2015 14:41:08 GMT

Thank you!!

Re: identifying observations 12 month period

jenim514 — Tue, 31 May 2016 20:20:53 GMT

Hi @MikeZdeb. A while ago you gave the the solution to a question I have about identifying observations in a rolling 12 month period. Can you help me modify this syntax to identify encounters that have 5 ER visits in any rolling 12 month period (regardless of diagnosis 'dx'). I feel like if should be similar to this syntax, but not quite sure how I should work this. All the observations in the table are ER visits, but I just need those that have 5 or more in any rolling 12 month period (table contains 5 years of ER data).

Thank you!!