proc sql

Ranjeeta — Fri, 29 Mar 2019 21:17:22 GMT

How can I modify the sql step below to include the REG_DT variable in my output dataset EVT_DQ9_FY2017

Thanks

PROC SQL;

CREATE TABLE EVT_DQ9_FY2017 AS

SELECT DISTINCT

A.*,

MISSING(A.INST_FROM_NO)=0 AS ENTERED_INST_FROM 'EVT hospital entered INST_FROM field',

MISSING(B.INST_NAME)=0 AS INST_FROM_TPA 'INST_FROM corresponds to AT/AM number of tPA Hospital',

MISSING(F.CIHI_KEY)=0 OR MISSING(H.CIHI_KEY)=0 AS TPA_PAT 'EVT patient previously seen at tPA Hospital',

MISSING(C.DSC_NAME)=0 AS TPA_PAT_ED 'EVT patient diagnosed with ischemic stroke in ED at tPA hospital',

DATEPART(A.ADM_DT)-DATEPART(C.REG_DT) AS DIFF_DAYS 'Days between ED registration at tPA hospital and admission into EVT hospital'

FROM EVT.EVT_FY2017 AS A

LEFT JOIN INST_DSC AS B

ON A.INST_FROM_NO='5'||STRIP(PUT(B.INST_AM,BEST32.)) OR A.INST_FROM_NO='5'||STRIP(PUT(B.INST_AM,BEST32.))

LEFT JOIN STROKE_NACRS_DSC AS C

ON A.HCNE=C.HCNE AND (DATEPART(A.ADM_DT)-DATEPART(C.REG_DT)>=0)

LEFT JOIN

( /* ANY DAD RECORD AT TPA SITE */

SELECT DISTINCT

D.*

FROM CIHI.DAD1718 AS D

INNER JOIN INST_DSC AS E

ON D.INST_NO=STRIP(PUT(E.INST_AT,BEST32.))

) AS F

ON A.HCNE=F.HCNE AND (DATEPART(A.ADM_DT)-DATEPART(F.ADM_DT)>=0)

LEFT JOIN

( /* ANY NACRS RECORD AT TPA SITE */

SELECT DISTINCT

N.*

FROM CIHI.NACRS1718 AS N

INNER JOIN INST_DSC AS G

ON N.INST_NO=STRIP(PUT(G.INST_AM,BEST32.))

) AS H

ON A.HCNE=H.HCNE AND (DATEPART(A.ADM_DT)-DATEPART(H.REG_DT)>=0); ;

QUIT;

Re: proc sql

ballardw — Fri, 29 Mar 2019 21:24:07 GMT

@Ranjeeta wrote:

How can I modify the sql step below to include the REG_DT variable in my output dataset EVT_DQ9_FY2017

Thanks

Which one? You have at least 2 available one as C.Reg_dt and the other as H.Reg_dt?

Did you try something like?:

SELECT DISTINCT

            A.*,   C.Reg_dt, 

            MISSING(A.INST_FROM_NO)=0 AS ENTERED_INST_FROM 'EVT hospital entered INST_FROM field',