READM HELP

[tmm]

The Code:

/************MERGE CLAIMS WITH MBR_SYS_ID ************/

PROC SQL;

CREATE TABLE ReAdm.Claims_Merge AS

(SELECT DISTINCT

1. A.*,
2. B.ADMIT_DT FORMAT=DATE8.,

CASE WHEN B.DISCH_DT IS NULL THEN A.FST_SRVC_DT ELSE B.DISCH_DT END AS DISCH_DT FORMAT=DATE8.,

CASE WHEN B.DISCH_DT IS NOT NULL THEN B.DISCH_DT+30 END AS CUTPUT_DT FORMAT=DATE8.,

CASE WHEN B.DISCH_DT IS NOT NULL THEN B.DISCH_DT+1 END AS START_DT FORMAT=DATE8.,

1. B.STAT_DAY

FROM ReAdm.Claims A

LEFT JOIN ReAdm.Claims_Inpt B

ON A.MBR_SYS_ID = B.MBR_SYS_ID

AND

1. A.FST_SRVC_DT BETWEEN B.ADMIT_DT AND B.DISCH_DT)

ORDER BY MBR_SYS_ID, STAT_DAY DESC;

QUIT;

I have the claims table and the claims_inpt table. When merging the goal is to calculate the readmissions but they can only count +1 from the discharge date up to 30days. So if the member has multiple admissions and the table looks like this

member                      admit                    dischrg

1111                          01jan10                  11jan10

1111                          11jan10                  15jan10

2222                          2feb10                    3feb10

2222                          7feb10                    15feb10

2222                          17feb10                   18feb10

etc..........1111 would have no readmit because the dischrg and admit on the 2nd admit is the same. It has to be plus 1. But 2222 would count 2 times because the 3feb10 to 7feb10 is between 1 and 30days and the 15feb10 and 17feb is between 1 and 30days. I was told to put in a case statement with a start_dt off discharge in addition to the code above which I did. That is the 3rd case statement. Leaving out the 3rd case statement when I run this along with code after counts those that were discharged and readmitted the same day. This gives me over 10k readmits which is wrong. I should have a total of 3869 readmits not counting people who were discharged and readmitted the same day. But the help I was given by putting in the addtl case statement did not work. it returns nothing when i am done. I tried chnging the 2nd case statement to say +1 instead of +30 and that lowered my readmits to 7500 but still wrong. Not sure what else to try and the person that offered advice is gone for good. So cannot get them to look at it.

[Reeza]

I think you're going about this inefficiently. Try using a SQL merge instead. It would be nice if you also had an admission id of some kind to help count the number of readmissions per initial admission.

See if the following gets you any closer. The problem is the 2nd admission for member 2222 would also have a 'readmission' but you could filter those out if you needed.

proc sql;

    create table want as

    select a.*, b.admit as readmit_start, b.dischrg as readmit_end

    from have a

    left join have b

    on (b.admit between a.dischrg+1 and a.dischrg+30)

    and a.member = b.member;

quit;

[tmm]

That does not work cuz a does not have admit or dischrg. B contains that information. maybe i should try a.fst_srvc_dt between b.dischrg+1 and b.dischrg+30C....................nope that does not work. The person that gave me the way I did it above it worked perfect until we found out that the requestor wanted to not include patients that were discharged and readmitted the same day. they wanted to wait 1 day after discharge. so they told me at a start_dt to the case and then put that var in the next series of code. they told me they ran it on their machine and perfect and got the 3689 readmits we should get. the only problem is they did not share the code they exactly used and I cannot figure it out and they are gone to India for a month on vacation. So there is no way for me to get their code and I cannot wait a month since this project is due monday.

[Reeza]

Well, the solution is based on the data you've posted.

Why don't you post a sample as a data step (so I don't have to type the data out) that shows what file 1 looks like, what file 2 looks like and what you expect the output to be. That'll be easier for ppl to deal with.

[tmm · Accepted solution]

Nevermind, I figured it out. Here is the code based on above:

/************MERGE CLAIMS WITH MBR_SYS_ID ************/

PROC SQL;

CREATE TABLE ReAdm.Claims_Merge AS

(SELECT DISTINCT

1. A.*,
2. B.ADMIT_DT FORMAT=DATE8.,

CASE WHEN B.DISCH_DT IS NULL THEN A.FST_SRVC_DT ELSE B.DISCH_DT END AS DISCH_DT FORMAT=DATE8.,

CASE WHEN B.DISCH_DT IS NOT NULL THEN B.DISCH_DT+30 END AS CUTPUT_DT FORMAT=DATE8.,

CASE WHEN B.DISCH_DT IS NOT NULL THEN B.DISCH_DT+1 END AS START_DT FORMAT=DATE8.,

1. B.STAT_DAY

FROM ReAdm.Claims A

LEFT JOIN ReAdm.Claims_Inpt B

ON A.MBR_SYS_ID = B.MBR_SYS_ID

AND

1. A.FST_SRVC_DT BETWEEN B.ADMIT_DT AND B.DISCH_DT)

ORDER BY MBR_SYS_ID, STAT_DAY DESC;

QUIT;

Then I have the rest of the code that is:

PROC SORT NODUPKEY; BY MBR_SYS_ID--PROV_EFF_DT;

RUN;

DATA READM.CLAIMS_INPT2; SET READM.CLAIMS_INPT;

READMIT_DT=ADMIT_DT;

REDISCH_DT=DISCH_DT;

START_DT2=DISCH_DT+1;

CUTPUT_DT2=DISCH_DT+30;

FORMAT READMIT_DT DATE8. REDISCH_DT DATE8. /*COUNT_DT DATE8.*/ START_DT2 DATE8. CUTPUT_DT2 DATE8.;

KEEP READMIT_DT REDISCH_DT MBR_SYS_ID COUNT_DT CUTPUT_DT2 START_DT2 ;

RUN;

PROC SQL;

CREATE TABLE ReAdm.Claims_Merge2 AS

(SELECT DISTINCT

1. A.*,
2. A.ADMIT_DT FORMAT=DATE8.,
3. B.START_DT2 FORMAT=DATE8.,
4. B.READMIT_DT FORMAT=DATE8.,
5. B.REDISCH_DT FORMAT=DATE8.,
6. B.CUTPUT_DT2 FORMAT=DATE8.

FROM ReAdm.Claims_Merge A

LEFT JOIN ReAdm.Claims_Inpt2 B

ON A.MBR_SYS_ID = B.MBR_SYS_ID

AND

1. B.READMIT_DT BETWEEN A.START_DT AND A.CUTPUT_DT)

ORDER BY MBR_SYS_ID, STAT_DAY DESC;

QUIT;

PROC SORT NODUPKEY; BY MBR_SYS_ID--PROV_EFF_DT;

RUN;

By including the start_dt var and doing it off the disch_dt+1 I get to beging counting the readmits 1 day after the patient is discharged. So instead of 10k plus readmits due to counting the disch_dt as a readmit date because the patients were readmitted, just readmitted the same day, I end up with a count of 3454 readmits after getting rid of the dupes. Dupes as in the member might have 10 readmits between that 1 and 30 days but they only get a count of 1. So my code is working great now and I got what I need. Thanks.