PROC SQL;
CREATE TABLE FLAGS AS
SELECT DISTINCT
A.*,
CASE WHEN MISSING(D.CIHI_KEY)=0 THEN 'DAD'
WHEN MISSING(N.CIHI_KEY)=0 THEN 'NACRS'
ELSE ''
END AS TRANS_TYPE,
CASE WHEN MISSING(sn.CIHI_KEY)=0 THEN 'strokenacrs'
ELSE ''
END AS STROKE_NACRS,
CASE WHEN MISSING(dsc.CIHI_KEY)=0 THEN 'strokenacrsdsc'
ELSE ''
END AS Stroke_nacrs_dsc
FROM FinalDADAbstracts AS A
LEFT JOIN CIHI.DAD1819Q4 AS D
ON A.HCNE=D.HCNE AND A.HCNE~='9999999999'
AND (DATEPART(D.DISCH_DT)<=DATEPART(A.ADM_DT)<=DATEPART(D.DISCH_DT)+1)
LEFT JOIN CIHI.NACRS1819Q4 AS N
ON A.HCNE=n.HCNE AND A.HCNE~='9999999999'
AND (DATEPART(n.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(n.Reg_DT)+1)
LEFT JOIN STROKE_NACRS(where=(STROKE_ISC=1))AS sn
ON A.HCNE=sn.HCNE AND A.HCNE~='9999999999'
AND (DATEPART(sn.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(sn.Reg_DT)+1)
LEFT JOIN Stroke_nacrs_dsc AS dsc
ON A.HCNE=dsc.HCNE AND A.HCNE~='9999999999'
AND A.CIHI_KEY~=dsc.CIHI_KEY
AND (DATEPART(dsc.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(dsc.Reg_DT)+1)
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.)))
/*where TRANS_INST_NACRS in ('4452','4248','4107','4131','4044','4268','4063','4144','4200','4845','4171','4138','4618','3982','4734','4071','4073','4238','4097','4124','3987','3972','4137','4168','4264','4123','4685','4245','4870')
or TRANS_INST_DAD IN ('1206','4415','4675','4451','2103','3734','3944','1946','1777','4059','1160','2110','4844','3932','2046','4616','4213','4730','1804','1768','1223','3988','2150','1825','4407','4353','1754','2207','3414','4681','3929','4871')*/
ORDER BY A.CIHI_KEY;
QUIT;
In the abve code , I would appreciate if you can advise if there is a way to include some variables from for e.g. the stroke nacrs dataset in the flag instead of using it in the where condition to subset the dataset the way i have done
I have a few other flags in the stroke nacrs dataset that i want to include as well so in the end i can get a count for each case if it saisfies all the conditions
If I'm understanding you correctly, you want to filter the data but not do it with your WHERE clause?
You would have to do that within a JOIN; just put AND and then your WHERE filter.
E.g.
AND TRANS_INST_NACRS in ('4452','4248','4107','4131','4044','4268','4063','4144','4200','4845','4171','4138','4618','3982','4734','4071','4073','4238','4097','4124','3987','3972','4137','4168','4264','4123','4685','4245','4870')
If you are wanting to do a subquery (I don't think SAS allows this?) then you might have to look at doing that with SQL Pass Through (I'm no SAS expert though) but that's likely the route I would take given I come from a SQL background.
Did you mean records in STROKE_NACRS only or STROKE_NACRS_DSC as well?
I suspect that you may want to add that condition to
LEFT JOIN STROKE_NACRS(where=(STROKE_ISC=1))AS sn
To subset the data earlier such as
LEFT JOIN STROKE_NACRS(where=(STROKE_ISC=1) and
(TRANS_INST_NACRS in ('4452','4248','4107','4131','4044','4268','4063','4144','4200','4845','4171','4138','4618','3982','4734','4071','4073','4238','4097','4124','3987','3972','4137','4168','4264','4123','4685','4245','4870')
or TRANS_INST_DAD IN ('1206','4415','4675','4451','2103','3734','3944','1946','1777','4059')
)AS sn
Note that TRANS_INST_dat is short because this forum makes it very hard to select some text that extends so far to the right that it is out of the window. But you should get the idea.
I want to include all the cases from stroke nacrs even if they did not atisfy the requirement for STROKE_ISC=1
but i want to add another flag to my final dataset such that stroke_ISC=1 cases are flagged seperately
Why not this?
PROC SQL; CREATE TABLE FLAGS AS SELECT DISTINCT A.*, sn.STROKE_ISC CASE WHEN MISSING(D.CIHI_KEY)=0 THEN 'DAD' WHEN MISSING(N.CIHI_KEY)=0 THEN 'NACRS' ELSE '' END AS TRANS_TYPE, CASE WHEN MISSING(sn.CIHI_KEY)=0 THEN 'strokenacrs' ELSE '' END AS STROKE_NACRS, CASE WHEN MISSING(dsc.CIHI_KEY)=0 THEN 'strokenacrsdsc' ELSE '' END AS Stroke_nacrs_dsc FROM FinalDADAbstracts AS A LEFT JOIN CIHI.DAD1819Q4 AS D ON A.HCNE=D.HCNE AND A.HCNE~='9999999999' AND (DATEPART(D.DISCH_DT)<=DATEPART(A.ADM_DT)<=DATEPART(D.DISCH_DT)+1) LEFT JOIN CIHI.NACRS1819Q4 AS N ON A.HCNE=n.HCNE AND A.HCNE~='9999999999' AND (DATEPART(n.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(n.Reg_DT)+1) LEFT JOIN STROKE_NACRS /*(where=(STROKE_ISC=1))*/ AS sn ON A.HCNE=sn.HCNE AND A.HCNE~='9999999999' AND (DATEPART(sn.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(sn.Reg_DT)+1) LEFT JOIN Stroke_nacrs_dsc AS dsc ON A.HCNE=dsc.HCNE AND A.HCNE~='9999999999' AND A.CIHI_KEY~=dsc.CIHI_KEY AND (DATEPART(dsc.Reg_DT)<=DATEPART(A.ADM_DT)<=DATEPART(dsc.Reg_DT)+1) 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.))) /*where TRANS_INST_NACRS in ('4452','4248','4107','4131','4044','4268','4063','4144','4200','4845','4171','4138','4618','3982','4734','4071','4073','4238','4097','4124','3987','3972','4137','4168','4264','4123','4685','4245','4870') or TRANS_INST_DAD IN ('1206','4415','4675','4451','2103','3734','3944','1946','1777','4059','1160','2110','4844','3932','2046','4616','4213','4730','1804','1768','1223','3988','2150','1825','4407','4353','1754','2207','3414','4681','3929','4871')*/ ORDER BY A.CIHI_KEY; QUIT;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: