I would suggest dropping the commas in the list as well. The IN operator no longer requires comma separated values and there are places where commas in a macro variable, such as passing as a parameter in macro code, will cause things to break.   Also I would suggest that since IN requires the parentheses that code attempting to use the macro list should look like: where= (hcpcs in ( &hcpc. ) ) instead of placing the () in the macro value.  ... View more

The main issue is that your SAS syntax is invalid.  You cannot use functions to replace code.  In this case you cannot call the SCAN() function at a place where SAS needs the name of a dataset.  You need to either use macro functions that generate the code or use regular SAS code to write the generated code to a file and %INCLUDE the file to run the generated code.   The syntax of the SCAN() function call also does not make much sense as you are trying to scan the value of a variable named IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY instead of the text string "IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY".   So if you change to using the %SCAN() macro function instead then your code will generate valid SAS code. %let etls_jobName = IF_04_J_PRT_TRA_INSURANCE_RECORD_DELIVERY; proc sql ; create table dis_entity_id as select distinct ENTITY_ID format=$ENTITY. from %scan(&etls_jobName.,6,'_') ; Which will read from the dataset named INSURANCE since that is the 6th word in your macro variables's value.   Note the + symbol in your log makes it look like you are already including this code from a file. Perhaps you can just fix the step that generates that file?     ... View more