Error with If condition because of special chars in macro variables

[sasuser_01]

Refer to table listed all the way below. Am reading values of column name : data and it has all kinds of special characters in it and am storing it in macro variable name: chklist. I was able to see the values getting stored in chklist  successfully in  logs. Am getting error while trying to call macro variable in data step when am trying to use it in if condition. Any suggestions on how to resolve it..

proc sql noprint;
select data into :chklist separated by ','
from outdata.dnt_update_b08;
quit;
%let chklist1=%superq(chklist); 

%put &chklist1;

data ibs_test2;
set ibs_test;

if cmf_name in (&chklist1.) then do;
FLAG ='Y';
end;
output;
run;

Error:

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,

a missing value, iterator, (, ), ',', :.

Table

key	data
A	(10/19
A	****FRAUD
A	***CLOSED
A	**ATTN
A	*_______________________
A	%    STUDENT  NAME
A	% LOFT
A	% FRANK
A	%PUBLIX
A	%UNIVERSAL
A	? DISNEY
A	'___________________
A	"__________________

[Kurt_Bremser]

Whenever you get an ERROR or WARNING, post the complete (all code and messages) log of the failing step by copy/pasting it into a window opened with this button:

All individual characters within an IN list must be contained in quotes. If the value contains single quotes, use double quotes around it, and vice versa. You can use a CASE clause in your SQL to do this conditionally.

[Tom]

How would you write the IF statement without any macro variables?

Probably something like this.

if cmf_name in ('value1' 'value2') then do;

So create the macro variable so that it has similar strings.  If you are worried about macro triggers like & or % in the strings then make sure to use single quotes instead of double quotes.

select distinct quote(trim(data),"'") into :chklist separated by ' '
  from outdata.dnt_update_b08
;

Notice also:

There is no need to add commas between the values.  SAS is happy to let you use spaces instead.  That way you don't have to fight to copy the value of the macro variable in places where commas might have special meaning.

There is no need to include the trailing spaces that SAS stores in character variables.  SAS string comparison ignore the trailing spaces.

There is no need to add macro quoting to the macro variable.  When the strings are quoted with single quotes the macro processor will ignore the content of the strings.

There is a 64K byte limit to the length of a macro variable. So make sure that there are not too many values of DATA (or that the values are not too long).

[Patrick · Accepted solution]

Issue 1: In the code shared the macro variable you populate is not the macro variable you use (&chklist vs. &chklist1)

Issue 2: The text strings need quoting.

Your in operator needs something like: 

if cmf_name in ('string 1', 'string2') then do;

The value in the macro variable you populate is missing the quotes and looks like:

string 1,string 2

Change your SQL to also quote the strings.

Issue 3: Use of macro quoted macro variable with in operator

Not all functions and operators deal (ignore) macro quoted strings. Either don't quote your macro variable or then unquote it for use with the in operator. With your current code and a quoted macro var this would need to look like:

Below some working sample code as template for you.

proc sql noprint;
  select cats("'",name,"'") into :chklist separated by ','
    from sashelp.class(obs=2);
quit;
%put %nrbquote(&chklist);

data demo;
  set sashelp.class;
  if name in (&chklist);
run;

And last but not least: For the rare case where you need to preserve leading blanks use catt() instead of cats().