topic Re: Select qualified records dynamically based on some criteria in SAS Procedures

Select qualified records dynamically based on some criteria

deleted_user — Sun, 30 May 2010 23:01:38 GMT

Hi,

I have a patient admission dataset. Each patient had multiple admissions (total number of admission are different from patient to patient and time interval between two admissions also varies).

This is what I am trying to do:

1, select those patients who had more than 3 admissions within 1 year block.
2. For qualified patient, flag their admissions in that year and the qaulifed 1st admission date as an index admission.

Admission dataset

Patient Admission_date Admssion_count
1 1/5/2006 1
1 2/9/2006 2
1 8/14/2006 3
2 8/19/2006 1
2 12/28/2006 2
2 9/11/2007 3
2 1/9/2007 4
2 11/21/2008 5
3 1/2/2006 1
3 5/18/2006 2
3 3/9/2007 3
3 6/29/2007 4
3 12/26/2007 5
3 2/9/2008 6

I need code to produce the following dataset:

patient Admission_date index_adm Admission_count
1 1/5/2006 1/5/2006 1
1 2/9/2006 1/5/2006 2
1 8/14/2006 1/5/2006 3
3 3/9/2007 3/9/2007 3
3 6/29/2007 3/9/2007 4
3 12/26/2007 3/9/2007 5

Patient 1 is selected because 3 admissions incurred within one year. Although Patient 3 had 6 admissions, only admission 3, 4 and 5 incurred within one year so only these records are selected. Patient 2 won't select because none of 3 admissions incurred within one year.

Any help are highly appreciated!
Frank

Re: Select qualified records dynamically based on some criteria

Cynthia_sas — Sun, 30 May 2010 23:10:47 GMT

Hi:
Is there a chance that the data will be "bad"?? For example, look at patient #2's data:
[pre]
2 8/19/2006 1
2 12/28/2006 2
2 9/11/2007 3
2 1/9/2007 4
2 11/21/2008 5
[/pre]

Admission count #3 is 9/11/2007, but admission count #4 is 1/9/2007 (before #3). So does the data need to be "scrubbed" first to make sure that the dates in the file are in the right order, regardless of admission_count value???

cynthia

Re: Select qualified records dynamically based on some criteria

Ksharp — Mon, 31 May 2010 02:17:12 GMT

Hi.
I agree with Cynthia .

The date of fourth admission is 1/9/2007 for number 2 patient.
However,The date of third admission is 9/11/2007.
The third admission is late to the fourth admission. Is it right?

Re: Select qualified records dynamically based on some criteria

deleted_user — Mon, 31 May 2010 03:25:52 GMT

Hi Cynthia and Ksharp,

Both of you are correct. Admission #4 should be 11/9/2007. Sorry about Typo and confusion it has caused.

Thank so much!

Frank

Re: Select qualified records dynamically based on some criteria

deleted_user — Mon, 31 May 2010 18:04:11 GMT

Figured it out by using lag2 function. Wondering if there is a better solution.
DATA admission;
informat admission_date mmddyy10.;
format admission_date mmddyy10.;
INPUT Patient admission_date Admssion_count ;
DATALINES;
1 1/5/2006 1
1 2/9/2006 2
1 8/14/2006 3
2 8/19/2006 1
2 12/28/2006 2
2 9/11/2007 3
2 11/9/2007 4
2 11/21/2008 5
3 1/2/2006 1
3 5/18/2006 2
3 3/9/2007 3
3 6/29/2007 4
3 12/26/2007 5
3 2/9/2008 6
;
run;
data admission1;
set admission;
by Patient;
Index_adm = lag2(admission_date);
if Admssion_count in (1, 2) then Index_adm = . ;
format Index_adm mmddyy10.;
if 1 <= admission_date - Index_adm <=365 then output;
run;
data admission2;
set admission1;
by Patient;
if first.Patient;
run;
proc sql;
create table admission3 as
select a.*
from admission a right join admission2 b
on a.Patient = b.Patient and a.admission_date between b.Index_adm and b.admission_date
order by Patient, Admssion_count;
quit;

Re: Select qualified records dynamically based on some criteria

deleted_user — Mon, 31 May 2010 18:29:55 GMT

Figured it out. But looking for a better solution.

DATA admission;
informat admission_date mmddyy10.;
format admission_date mmddyy10.;
INPUT Patient admission_date Admssion_count ;
DATALINES;
1 1/5/2006 1
1 2/9/2006 2
1 8/14/2006 3
2 8/19/2006 1
2 12/28/2006 2
2 9/11/2007 3
2 11/9/2007 4
2 11/21/2008 5
3 1/2/2006 1
3 5/18/2006 2
3 3/9/2007 3
3 6/29/2007 4
3 12/26/2007 5
3 2/9/2008 6
;
run;

data admission1;
set admission;
by Patient;
Index_adm = lag2(admission_date);
if Admssion_count in (1, 2) then Index_adm = . ;
format Index_adm mmddyy10.;
if (admission_date - Index_adm) ge 1 and (admission_date - Index_adm) le 365 then output;
run;

data admission2;
set admission1;
by Patient;
if first.Patient;
run;

proc sql;
create table admission3 as
select a.*
from admission a right join admission2 b
on a.Patient = b.Patient and
a.admission_date between b.Index_adm and b.admission_date
order by Patient, Admssion_count;
quit;

Re: Select qualified records dynamically based on some criteria

Peter_C — Tue, 01 Jun 2010 14:30:36 GMT

if you allow "year blocks" to overlap, the situation becomes complex. Consider the following admission dates for one patient:
march 07, september 07, october 07, January 08
The pair of admissions in Sept-07 and Oct-07 may be within "year blocks" starting Mar-07 and/or Sep-07. Is this restricted? On what basis?
What if the dates sequence places the 4th closer to the 3rd than the 2nd is to the first. E.G. Jan-07, Nov-07, Dec-07, Jan-08
In that case Nov-07 to Jan-08 looks more of an episode than Jan-07 to Dec-07.
If you want "every year block including 3 admissions", then that will involve overlapping blocks. Is that required? If not, on what basis should there be restriction?
My first example included 4 admissions within 12 months.
Is that treated as one group? When there are, say 9 admissions within 12 months, is that 3 episodes or one?

sorry to start asking questions when you seem to be close to closure 😉

peterC

Re: Select qualified records dynamically based on some criteria

Ksharp — Tue, 01 Jun 2010 14:54:18 GMT

Hi,FrankL
I think your code has something wrong.
Such as ,
No, 3 patient should have four obs.
3 3/9/2007 3
3 6/29/2007 4
3 12/26/2007 5
3 2/9/2008 6

3/9/2007 - 3/9/2008 should be one year .
However, your code only have
3 3/9/2007 3
3 6/29/2007 4
3 12/26/2007 5

I am not sure whether i understand your mean.

Re: Select qualified records dynamically based on some criteria

deleted_user — Thu, 03 Jun 2010 02:08:38 GMT

Hi Peter,

The following is my clarification. Thanks for reviewing my posting.

The pair of admissions in Sept-07 and Oct-07 may be within "year blocks" starting Mar-07 and/or Sep-07. Is this restricted? On what basis?

No, as long as 3 admissions are within 365 days.

What if the dates sequence places the 4th closer to the 3rd than the 2nd is to the first. E.G. Jan-07, Nov-07, Dec-07, Jan-08
In that case Nov-07 to Jan-08 looks more of an episode than Jan-07 to Dec-07

No. In this case, Jan-07, Nov-07, Dec-07 will be selected because it is the 1st episode.

My first example included 4 admissions within 12 months.
Is that treated as one group? When there are, say 9 admissions within 12 months, is that 3 episodes or one?

One episode. One episode is defined as "3 or more admissions within 365 days".

Thanks!
--Frank

Re: Select qualified records dynamically based on some criteria

deleted_user — Thu, 03 Jun 2010 02:33:05 GMT

Hi Ksharp,

You are correct. See below for new code.

proc sql;
create table admission3 as
select a.*
from admission a right join admission2 b
on a.Patient = b.Patient and
a.admission_date between b.Index_adm and (b.Index_adm+365)
order by Patient, Admssion_count;
quit;

Thanks for review it!
--Frank

Re: Select qualified records dynamically based on some criteria

Ksharp — Thu, 03 Jun 2010 11:22:23 GMT

Hi . Frank
I recode it based your sql.
I found proc sql's Cartesian Product is very usful...
I like sas. I like proc sql very much.

[pre]
DATA admission;
informat admission_date mmddyy10.;
format admission_date mmddyy10.;
INPUT Patient admission_date Admssion_count ;
DATALINES;
1 1/5/2006 1
1 2/9/2006 2
1 8/14/2006 3
2 8/19/2006 1
2 12/28/2006 2
2 9/11/2007 3
2 11/9/2007 4
2 11/21/2008 5
3 1/2/2006 1
3 5/18/2006 2
3 3/9/2007 3
3 6/29/2007 4
3 12/26/2007 5
3 2/9/2008 6
;
run;

proc sql;
select distinct b.*
from admission as a,admission as b
where a.patient eq b.patient and
(b.admission_date between a.admission_date and a.admission_date+365)
group by a.admission_date
having count(*) ge 3
order by b.patient;
quit;
[/pre]

distinct in proc sql can be optional.
Ksharp Message was edited by: Ksharp

Re: Select qualified records dynamically based on some criteria

Peter_C — Thu, 03 Jun 2010 11:50:02 GMT

FrankL

thank you for your response ......
Where I asked.
> ......... When there are, say 9
> admissions within 12 months, is that 3 episodes or
> one?

You respond
> One episode. One episode is defined as "3 or more
> admissions within 365 days".

Why should monthly visits from January to September not be treated as 7 episodes each episode having 3or more visits and starting on a different start date- ?
Jan - Sep =9 visits
Feb - Sep =8 visits
Mar - Sep =7
....
July - Sep =3 visits

For a less easily dismissed example of a patient with 4 visits:
June, Sept, Mar, July
Is this 2 episodes, June-Mar and Sept-July ?
Or, once a visit is assigned to an episode, does it become attached to that episode and excluded from other episodes?

Peter

Re: Select qualified records dynamically based on some criteria

Ksharp — Fri, 04 Jun 2010 01:46:47 GMT

I check Frank's code too. Find it only can test the first episode having 3or more visits.
But I found proc sql'sCasteric Product is very useful..