Search code examples
sas

SAS: create list of strings in quotation marks as macro variable for input filtration

I'd like to use the following syntax

data new;
   set old (where=(mystring in ('string1','string2',...,'string500')));
run;

in order to filter a very large input data set. The 500 strings at first are contained as numeric values in the variable "bbb" in the dataset "aux". So far I have created a macro variable which contains the required list of the 500 strings the following way:

proc sql noprint;
   select bbb into :StringList1 separated by "',' "
   from work.aux; 
quit;
data _null_; call symputx('StringList2',compress("'&StringList1'")); run; 

data new;
   set old (where=(mystring in (&StringList2)));
run;

... which seems to work. But there is a warning telling me that

The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks.

Results still seem to be plausible. Should I be worried that one day results might become wrong?

More importantly: I try to find a way to avoid using the compress function by setting up the 

separated by "',' "

option in a way that does not contain blanks in the first place. Unfortunately the following seems not to work:

separated by "','"

It doesn't give me a eror message but when looking at the macro variable there is a multipage-mess of red line numbers (the color which usually denotes error messages), empty rows, minus signs, ... . The following screenshot shows part of the log after running this code:

proc sql noprint;
   select vnr into :StringVar1 separated by "','"
   from work.var_nr_import;
quit;
%put &StringVar1.;

enter image description here

Have already tried to make use of the STR()-function but no success so far.

Solution

  • I cannot replicate your error messages in SAS 9.3

    If your variable is numeric you don't need quotes in the macro variable.

    If it is character try using the QUOTE() function. 

     proc sql noprint;
 select quote(bbb) into :StringList1 separated by " "
 from work.aux; 
 quit;