proc sql

[Ranjeeta]

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;

[ballardw · Accepted solution]

---

@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',