Select procedure

[Ranjeeta]

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 

[PGStats]

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)

[Ranjeeta]

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

[PGStats]

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

[Kurt_Bremser]

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.