topic Re: Select procedure in SAS Programming

Select procedure

Ranjeeta — Thu, 30 Apr 2020 18:41:27 GMT

proc sql; create table vas_DAD1819 as
	select 'FY1819' as FY, CIHI_KEY, HCNE, ADM_DT,
			INTERV_CODE_01, INTERV_CODE_02, INTERV_CODE_03, INTERV_CODE_04, INTERV_CODE_05,
			INTERV_CODE_06, INTERV_CODE_07, INTERV_CODE_08, INTERV_CODE_09, INTERV_CODE_10,
			INTERV_CODE_11, INTERV_CODE_12, INTERV_CODE_13, INTERV_CODE_14, INTERV_CODE_15,
			INTERV_CODE_16, INTERV_CODE_17, INTERV_CODE_18, INTERV_CODE_19, INTERV_CODE_20
	from test.DAD1819
	where substr (INTERV_CODE_01,1,5) in (select CCI_code_new from vas_code)
		OR substr (INTERV_CODE_02,1,5) in (select CCI_code_new from vas_code)
;
quit;
proc sql; create table vas_DAD1819 as
	select 'FY1819' as FY, a.CIHI_KEY, HCNE, a.ADM_DT,
			a.INTERV_CODE_01, a.INTERV_CODE_02, a.INTERV_CODE_03, a.INTERV_CODE_04, a.INTERV_CODE_05,
			a.INTERV_CODE_06, a.INTERV_CODE_07, a.INTERV_CODE_08, a.INTERV_CODE_09, a.INTERV_CODE_10,
			a.INTERV_CODE_11, a.INTERV_CODE_12, a.INTERV_CODE_13, a.INTERV_CODE_14, a.INTERV_CODE_15,
			a.INTERV_CODE_16, a.INTERV_CODE_17, a.INTERV_CODE_18, a.INTERV_CODE_19, a.INTERV_CODE_20,
			b.*
	from test.DAD1819 as a left join Vas_code as b
	on substr(a.INTERV_CODE_01,1,5) in 
			(select CCI_code_new from vas_code)
	;
	quit;

Hello,

Would appreciate if you can advise if which of the following codes would do the below correctly:

I have select CCI code new in a seperate dataset

I want to select all cases from DAD1819 where any of the INTERV_CODE_01 to INTERV_CODE_20 fields have the codes from the CCI code new list

In the 1st code would it be correct to keep adding all the 20 variables i.e. INTERV_CODE_XX using the OR statement

Re: Select procedure

PGStats — Thu, 30 Apr 2020 22:13:16 GMT

Please try:

proc sql; 
	create table vas_DAD1819 as
	select  unique
			'FY1819' as FY, a.CIHI_KEY, HCNE, a.ADM_DT,
			a.INTERV_CODE_01, a.INTERV_CODE_02, a.INTERV_CODE_03, a.INTERV_CODE_04, a.INTERV_CODE_05,
			a.INTERV_CODE_06, a.INTERV_CODE_07, a.INTERV_CODE_08, a.INTERV_CODE_09, a.INTERV_CODE_10,
			a.INTERV_CODE_11, a.INTERV_CODE_12, a.INTERV_CODE_13, a.INTERV_CODE_14, a.INTERV_CODE_15,
			a.INTERV_CODE_16, a.INTERV_CODE_17, a.INTERV_CODE_18, a.INTERV_CODE_19, a.INTERV_CODE_20,
			b.*
	from 
		test.DAD1819 as a left join 
		Vas_code as b on 
			whichc ( 	CCI_code_new, 
				substr(a.INTERV_CODE_01,1,5),
				substr(a.INTERV_CODE_02,1,5),
				substr(a.INTERV_CODE_03,1,5),
				substr(a.INTERV_CODE_04,1,5),
				substr(a.INTERV_CODE_05,1,5),
				substr(a.INTERV_CODE_06,1,5),
				substr(a.INTERV_CODE_07,1,5),
				substr(a.INTERV_CODE_08,1,5),
				substr(a.INTERV_CODE_09,1,5),
				substr(a.INTERV_CODE_10,1,5),
				substr(a.INTERV_CODE_11,1,5),
				substr(a.INTERV_CODE_12,1,5),
				substr(a.INTERV_CODE_13,1,5),
				substr(a.INTERV_CODE_14,1,5),
				substr(a.INTERV_CODE_15,1,5),
				substr(a.INTERV_CODE_16,1,5),
				substr(a.INTERV_CODE_17,1,5),
				substr(a.INTERV_CODE_18,1,5),
				substr(a.INTERV_CODE_19,1,5),
				substr(a.INTERV_CODE_20,1,5) 
				) > 0
	;
quit;

(not tested)

Re: Select procedure

Ranjeeta — Fri, 01 May 2020 00:04:47 GMT

All the referenced fields are character How would i change all the fields to numeric

Re: Select procedure

PGStats — Fri, 01 May 2020 04:48:17 GMT

To convert the character codes to numbers in the output table, replace

a.INTERV_CODE_01,

with

input(a.INTERV_CODE_01, best.),

in the selected list of variables

Re: Select procedure

Kurt_Bremser — Fri, 01 May 2020 07:25:58 GMT

This is another classic example where bad dataset structure makes the SAS coder's life miserable. With a longitudinal dataset, the code would be much easier. Please supply example data for tables DAD1819 and VAS_CODE, so I can provide code.