Modifying a code

[Ranjeeta]

How would i modify the code below to capture the direct admisions i.e. FROM deno_2_EVTFacilities the patients that occur in this database i want to check if these patients appeared in any hospital (in either  cihi.dad1819q4 or cihi.nacrs1819q4) before the admission to the EVT hospital in EVT_fy2018q4 

So if they did not appear in any hospital then it would be classified as a direct admission otherwise  transfer

Any help in modifying the code below would be appreciated

PROC SQL;
CREATE TABLE SelfDirectEVT AS
SELECT DISTINCT
A.*,
N.REG_DT AS TRANS_REG_DT,
N.REG_TM AS TRANS_REG_TM,
D.ADM_DT AS TRANS_ADM_DT,
D.ADM_TM AS TRANS_ADM_TM,
COALESCE(N.INST_FROM,D.INST_FROM_NO) AS TRANS_INST_FROM,
D.INST_NO AS TRANS_INST_DAD,
N.INST_NO AS TRANS_INST_NACRS,
D.CIHI_KEY AS TRANS_KEY_DAD,
N.CIHI_KEY AS TRANS_KEY_NACRS,
E.INST_NAME AS TRANS_INST_NAME,
E.INST_ABB AS TRANS_INST_ABB,
E.FAC_NUM_NEW AS TRANS_FAC,
CASE
WHEN MISSING(D.CIHI_KEY)=0 THEN 'DAD'
WHEN MISSING(N.CIHI_KEY)=0 THEN 'NACRS'
ELSE ''
END AS TRANS_TYPE,
MISSING(D.CIHI_KEY)=0 AS DAD_TRANSFER,
MISSING(N.CIHI_KEY)=0 AS NACRS_TRANSFER,
MISSING(D.CIHI_KEY)=0 OR MISSING(N.CIHI_KEY)=0 AS ANY_TRANSFER,
C.EVT_DT,
C.EVT_TM,
MISSING(C.EVT_DT)=0 AS INDICATOR3 'Ischemic stroke case transferred from tPA site received EVT'
FROM deno_2_EVTFacilities AS A
LEFT JOIN cihi.dad1819q4(WHERE=(INST_NO IN (&EVT_INST_AT.))) AS D
ON A.HCNE=D.HCNE AND A.HCNE~='9999999999'
AND (DATEPART(A.REG_DT)<=DATEPART(D.ADM_DT)<=DATEPART(A.REG_DT)+1)
LEFT JOIN cihi.nacrs1819q4(WHERE=(INST_NO IN (&EVT_INST_AM.))) AS N
ON A.HCNE=N.HCNE AND A.HCNE~='9999999999'
AND A.CIHI_KEY~=N.CIHI_KEY
AND (0 <= INTCK('HOUR', A.REG_TM, N.REG_TM) <= 24)
LEFT JOIN EVT_fy2018q4 AS C
ON D.CIHI_KEY=C.CIHI_KEY
LEFT JOIN INST_EVT AS E
ON (D.INST_NO=STRIP(PUT(E.INST_AT,BEST32.)) OR N.INST_NO=STRIP(PUT(E.INST_AM,BEST32.)))
ORDER BY TRANS_FAC, A.CIHI_KEY;
QUIT;

[Shmuel · Accepted solution]

You are asking for:

"So if they did not appear in any hospital then it would be classified as a direct admission otherwise  transfer".

Am I correct that the given code selects the first group - to be classified as direct admission ?

What do you want to do with the other group? Do you want to create a new dataset ?

If positive just add another step:

proc sql;
    create table transfer as
    select *
    from deno_2_EVTFacilities 
    where cihi_key not in
      (select cihi_key from SelfDirectEVT)
; quit;