BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
12 REPLIES 12
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User
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?
deleted_user
Not applicable
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
deleted_user
Not applicable
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;
deleted_user
Not applicable
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;
Ksharp
Super User
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.
deleted_user
Not applicable
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
Ksharp
Super User
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
Peter_C
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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
Peter_C
Rhodochrosite | Level 12
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
Ksharp
Super User
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..

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1240 views
  • 0 likes
  • 4 in conversation