Combining Readmit with Original Visit Data

[shellp55]

Hello

I am using using SAS 9.3.

I have a database where I'm trying to determine readmits for hospital patients.  I consider the visit that links to the readmit as an "index" case.  The code below provides me with the field of "diff" and then I can select all those that match criteria of <= 30 days.  However, I wish to also report on the index case information so is there any way to have a field for "previous account number" or any previous data?

So what I'm asking is how to have the readmit case also show specifics from the index case all on the same line of data?  Thanks.

data test_grp;
input @1  chartno $6.
   @7  acctno  $3.
   @10 admdate yymmdd8.
   @19 sepdate yymmdd8.;
format admdate yymmdd10. sepdate yymmdd10.;
cards;
1111110012012032920120331
2222220022012050120120515
3333330032012060120120607
1111110042012040220120410
4444440052012060320120615
5555550062012053120120603
2222220072012070120120713
2222220082012072020120725
4444440092012081320120817
3333330102012061520120621
1111110112012110120121130
run;
proc sort data=test_grp; by chartno admdate;run;

Data readmits;
set test_grp;
by chartno;
dif=ifn(first.chartno,.,admdate-lag(sepdate));
format admdate ddmmyy10. sepdate ddmmyy10.;

run;

[Haikuo]

It would be helpful if you could provide a sample output that you want.

[TashaChapman]

I can think of a couple ways... you might consider something with the LAG function (which is similar to the DIF function, except it returns the actual value rather than the difference between the two values).  You also might be able to do something with SQL, maybe using a subquery.  Hai.kuo is right, though.  If you provide an example of what you'd like to see, that might help.

[shellp55]

Hello

So below is a sample of what I'd like to see i.e. when the difference between the previous separation date and the next visit admit date is <=30 days, then I want the previous account number to show up on the line of the 30 day readmit. 

I realized from my original code that I will need to sort by chartno but also admdate to ensure that the visits are in chronological order.  I'm hoping that whatever method is shown to me, I can then also extract previous diagnosis and triage level using that same method.

Thanks for any and all assistance.

[Reeza]

Assuming your data is in order, as specified the following is one way. It assumes you calculate an episode and visit number within episodes first.

It also deals with multiple visits within a 30 day period.

Data readmits;

set test_grp;

by chartno;

dif=ifn(first.chartno,.,admdate-lag(sepdate));

format admdate ddmmyy10. sepdate ddmmyy10.;

*create an episode;

if first.chartno then episode=1;

if dif > 30 then episode+1;

*count visits within episodes;

if first.chartno the visit=1;

else if dif <=30 then visit+1;

else visit=1;

*flag a readmit;

if dif<=30 and dif ne . then readmit=1;

run;

*merge in fields, you can add the other fields in the same manner as acctno;

proc sql;

create table readmits_with_index as

select r.*, i.acctno as index_visit

from readmits as r

left join readmits as i

on r.chartno=i.chartno

and r.episode=i.episode

and i.visit=1

and r.readmit=1

order by chartno, episode, visit;

quit;

[shellp55]

Thanks Reeza!  This works great.

[shellp55]

Hi Reeza

I spoke too soon.  With a larger database it appears that all subsequent readmits are linked to visit #1 whereas the readmit difference is still connected to the various visits. I added a visit for chartno 111111 below and you'll note that even though the 3rd visit is connected to the 2nd visit, it shows the account number of the first visit, which I don't want.  I want the 3rd visit to have the account number of the 2nd visit because that is the one that is within 30 days of it.  Thanks.

data test_grp;
input    @1  chartno $6.
   @7  acctno  $4.
   @11 admdate yymmdd8.
   @19 sepdate yymmdd8.
   @27 cmg     $3.;
format admdate yymmdd10. sepdate yymmdd10.;

cards;
111111A0012012032920120331001
222222A0022012050120120515003
333333A0032012060120120607321
111111A0042012040220120410320
444444A0052012060320120615109
555555A0062012053120120603149
222222A0072012070120120713001
222222A0082012072020120725026
444444A0092012081320120817027
333333A0102012061520120621028
111111A0112012110120121130004
111111A0122012050320120517001
run;
proc sort data=test_grp; by chartno admdate;run;

Data readmits;
set test_grp;
by chartno admdate;
length index_acctno $4.;
length index_cmg $3.;

dif=ifn(first.chartno,.,admdate-lag(sepdate));
if first.chartno then index_acctno="";
else index_acctno=lag(acctno);
if first.chartno then index_cmg="";
else index_cmg=lag(cmg);

format admdate ddmmyy10. sepdate ddmmyy10.;

run;

Data readmits;
set test_grp;
by chartno;
dif=ifn(first.chartno,.,admdate-lag(sepdate));
format admdate ddmmyy10. sepdate ddmmyy10.;

*create an episode;
if first.chartno then episode=1;
if dif > 30 then episode+1;

*count visits within episodes;
if first.chartno the visit=1;
else if dif <=30 then visit+1;
else visit=1;

*flag a readmit;

if dif<=30 and dif ne . then readmit=1;

run;

*merge in fields, you can add the other fields in the same manner as acctno;
proc sql;
create table readmits_with_index as
select r.*, i.acctno as index_visit
from readmits as r
left join readmits as i
on r.chartno=i.chartno
and r.episode=i.episode
and i.visit=1
and r.readmit=1
order by chartno, episode, visit;
quit;

[Reeza]

Ok, I assumed you'd want the first visit that started an episode, not the previous visit.

But you can change the SQL query join to get the previous visit by joining on the previous visit number rather than visit=1.

CHANGE on i.visit=1 TO

on i.visit=r.visit-1

to get the previous visit

or the other way around, I don't recall

on r.visit-1=i.visit 

[shellp55]

Hi Reeza

Changing it to i.visit=r.visit-1 creates duplicates of the visits and doing it the other way then gets the "previous acctno" to be the next acctno.

Any suggestions?  Thanks.

[Reeza]

You have an extra datastep in there and small typo (probably mine originally). It seems to work for me, assuming I understand correctly what you want.

data test_grp;
input    @1  chartno $6.
   @7  acctno  $4.
   @11 admdate yymmdd8.
   @19 sepdate yymmdd8.
   @27 cmg     $3.;
format admdate yymmdd10. sepdate yymmdd10.;

cards;
111111A0012012032920120331001
222222A0022012050120120515003
333333A0032012060120120607321
111111A0042012040220120410320
444444A0052012060320120615109
555555A0062012053120120603149
222222A0072012070120120713001
222222A0082012072020120725026
444444A0092012081320120817027
333333A0102012061520120621028
111111A0112012110120121130004
111111A0122012050320120517001
run;
proc sort data=test_grp; by chartno admdate;run;

Data readmits;
set test_grp;
by chartno admdate;
length index_acctno $4.;
length index_cmg $3.;

dif=ifn(first.chartno,.,admdate-lag(sepdate));
if first.chartno then index_acctno="";
else index_acctno=lag(acctno);
if first.chartno then index_cmg="";
else index_cmg=lag(cmg);

format admdate ddmmyy10. sepdate ddmmyy10.;

run;

Data readmits;
set test_grp;
by chartno;
dif=ifn(first.chartno,.,admdate-lag(sepdate));
format admdate ddmmyy10. sepdate ddmmyy10.;

*create an episode;
if first.chartno then episode=1;
if dif > 30 then episode+1;

*count visits within episodes;
if first.chartno the visit=1;
else if dif <=30 then visit+1;
else visit=1;

*flag a readmit;

if dif<=30 and dif ne . then readmit=1;

run;

*merge in fields, you can add the other fields in the same manner as acctno;
proc sql;
create table readmits_with_index as
select r.*, i.acctno as index_visit
from readmits as r
left join readmits as i
on r.chartno=i.chartno
and r.episode=i.episode
and i.visit=1
and r.readmit=1
order by chartno, episode, visit;
quit;

                                                                       index_

Obs chartno acctno    admdate    sepdate cmg dif episode visit readmit visit

  1 111111   A001  29/03/2012 31/03/2012 001   .    1      1      .

  2 111111   A004  02/04/2012 10/04/2012 320   2    1      2      1     A001

  3 111111   A012  03/05/2012 17/05/2012 001  23    1      3      1     A004

  4 111111   A011  01/11/2012 30/11/2012 004 168    2      1      .

  5 222222   A002  01/05/2012 15/05/2012 003   .    1      1      .

  6 222222   A007  01/07/2012 13/07/2012 001  47    2      1      .

  7 222222   A008  20/07/2012 25/07/2012 026   7    2      2      1     A007

  8 333333   A003  01/06/2012 07/06/2012 321   .    1      1      .

  9 333333   A010  15/06/2012 21/06/2012 028   8    1      2      1     A003

10 444444   A005  03/06/2012 15/06/2012 109   .    1      1      .

11 444444   A009  13/08/2012 17/08/2012 027  59    2      1      .

12 555555   A006  31/05/2012 03/06/2012 149   .    1      1      .

[shellp55]

Hi Reeza

Thanks for helping out but I copied exactly what you have and it still isn't working...I get where A001 shows up for both the 111111 charts that have valid readmits.

Where was the typo?  Thanks.

[DBailey]

data have;

input @1  chartno $6.

   @7  acctno  $3.

   @10 admdate yymmdd8.

   @19 sepdate yymmdd8.;

format admdate yymmdd10. sepdate yymmdd10.;

cards;

1111110012012032920120331

2222220022012050120120515

3333330032012060120120607

1111110042012040220120410

4444440052012060320120615

5555550062012053120120603

2222220072012070120120713

2222220082012072020120725

4444440092012081320120817

3333330102012061520120621

1111110112012110120121130

run;

proc sort in=have;

by chartno acctno;

run;

data want (drop=acctno_lag sepdate_lag);

set have;

by chartno;

format

    sepdate_lag date9.

    index_acctno $3.

    ;

index_acctno='';

acctno_lag=lag(acctno);

sepdate_lag=lag(sepdate);

if first.chartno then dif=.;

else do;

    dif = admdate - sepdate_lag;

    if coalesce(dif,0) <=30 then index_acctno=acctno_lag;

    end;

run;

[shellp55]

Thanks DBailey, this works great.

Now my other question is:  is there any way to add the readmit information to the index visit instead of adding the index visit information to the readmit?  I ask because if I run data for April to January then any readmits into January can be flagged on the visit by only running Apr to Dec original visit data.  Otherwise, I will always have to run the report for one less month to ensure I have readmits from the following month.

[DBailey]

perhaps by sorting by chartno admdate descending and then processing similarly?