FLAGS in proc sql

[Ranjeeta]

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 

[Krueger]

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.

[ballardw]

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.

[Ranjeeta]

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

[Krueger]

So remove any filter that shows the stroke ics (whatever it was im on my phone cant see) then add that column in your select list.

From there you can export the data and use a filter with excel or just requery the new dataset and place your filter on two different datasets.

[ChrisNZ]

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;