topic Re: Can't Pass Macro List Into Where Clause in SAS Programming

Can't Pass Macro List Into Where Clause

acemanhattan — Wed, 25 Sep 2019 04:17:55 GMT

I've tried no quotes, single quotes, double quotes, etc. but can't get my hcpc macro list to work in the code below. It tells me that there was a Syntax error while parsing WHERE clause.

Please help me fix my code, but also help me understand the general rule and what makes my first list different from my second.

libname hcgout '/CUDM/HCG/HCG_PROD/Data_Sets/Output_NET' ;
%let list = linenum memberid memberctg incurd_yr_mo hcpcs;
%let hcpc = "E0431", "E1390", "E1392", "K0738";


data hcgTables2014toPresent ;
	set hcgout.outclaims_2014 (where= (hcpcs in &hcpc.) keep=&list.)
		hcgout.outclaims_2015 (where= (hcpcs in &hcpc.) keep=&list.)
		hcgout.outclaims_prior (where= (hcpcs in &hcpc.) keep=&list.)		
		hcgout.outclaims_current (where= (hcpcs in &hcpc.) keep=&list.);
run;

Re: Can't Pass Macro List Into Where Clause

VinitvictorCorr — Wed, 25 Sep 2019 04:35:10 GMT

please try using %let hcpc = ("E0431", "E1390", "E1392", "K0738"); and let me know if it works, if it dosent, can you please attach the outclaims_2014 data set or at least an example so i could try to work on the solution

Re: Can't Pass Macro List Into Where Clause

34reqrwe — Wed, 25 Sep 2019 04:38:47 GMT

You need brackets around your list of hcpc

libname hcgout '/CUDM/HCG/HCG_PROD/Data_Sets/Output_NET' ;
%let list = linenum memberid memberctg incurd_yr_mo hcpcs;
%let hcpc = ("E0431", "E1390", "E1392", "K0738");


data hcgTables2014toPresent ;
set hcgout.outclaims_2014 (where= (hcpcs in &hcpc.) keep=&list.)
hcgout.outclaims_2015 (where= (hcpcs in &hcpc.) keep=&list.)
hcgout.outclaims_prior (where= (hcpcs in &hcpc.) keep=&list.) 
hcgout.outclaims_current (where= (hcpcs in &hcpc.) keep=&list.);
run;

Re: Can't Pass Macro List Into Where Clause

ChrisNZ — Wed, 25 Sep 2019 04:57:51 GMT

Always run the code without any macro language, and then replace values with macro variables.

As it is, your syntax for the in() statement is invalid, so there is not chance of anything running.

Once the syntax of hard-coded values is right, you can think about soft-coding with the macro language.

Re: Can't Pass Macro List Into Where Clause

ballardw — Wed, 25 Sep 2019 15:12:35 GMT

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.