BookmarkSubscribeRSS Feed
Ranjeeta
Pyrite | Level 9
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 

5 REPLIES 5
Krueger
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Pyrite | Level 9
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
Tourmaline | Level 20

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; 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 3670 views
  • 0 likes
  • 4 in conversation