CODEWITHSUNDEEP
sas
Joz
Joz

Reputation: 247

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.

Upvotes: 0

Views: 4258

Answers (3)

Venkata Sunil P
Venkata Sunil P

Reputation: 1

This will work,

for double quotations

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

for single quotations

proc sql noprint;
   select "'"||bb||"'" into :StringList1 separated by ","
   from work.aux; 
quit;

Upvotes: 0

Tom
Tom

Reputation: 51621

A macro variable can only contain 65,534 characters. So if there are too many values of BBB then your macro variable value will be truncated. This could lead to unbalanced quotes. That is most likely the source of your errors.

Note that you can turn off the warning about the length of the quoted strings by using the NOQUOTELENMAX system option, but in this application you wouldn't want to because the individual quoted strings are not that long.

You will be better served to use another method to subset your data if lists this long are required.

Upvotes: 1

Reeza
Reeza

Reputation: 21294

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;

Upvotes: 1

Related Questions