DATA Step, Macro, Functions and more

identifying observations 12 month period

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

identifying observations 12 month period

[ Edited ]

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.

 

 


Accepted Solutions
Solution
‎10-28-2015 10:41 AM
Valued Guide
Posts: 765

Re: identifying observations 12 month period

[ Edited ]

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

 

 

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: identifying observations 12 month period

If you have example data that will be helpful.

Frequent Contributor
Posts: 121

Re: identifying observations 12 month period

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

149390 0540905APR2014
2251249392 019DEC2013
349390 07288803JUN2014
349390 1309426OCT2013
4275249392 006OCT2013
549381 07225226NOV2013
649320 0722028APR2014
749390 05532919DEC2013
849390 04888208JUL2014
949049392 008DEC2013
Solution
‎10-28-2015 10:41 AM
Valued Guide
Posts: 765

Re: identifying observations 12 month period

[ Edited ]

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

 

 

Frequent Contributor
Posts: 121

Re: identifying observations 12 month period

Thank you!!

Frequent Contributor
Posts: 121

Re: identifying observations 12 month period

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

 

 

Valued Guide
Posts: 765

Re: identifying observations 12 month period

[ Edited ]

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 :'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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 390 views
  • 1 like
  • 3 in conversation