topic Re: Special characters in IN operator in SAS Programming

Special characters in IN operator

David_Billa — Wed, 31 May 2023 18:20:55 GMT

Is there a way to use IN operator to look for the values separated by pipe (|) instead of comma? Value of macro variable is separated by pipe and I want to use the same value in where clause of proc print. I cannot control the macro variable value with pipe as separators.

Looking for dynamic solution when macro variable has either 1 or 3 or 4 values. e.g. ("Banking" or "Banking"|"Insurance"|"Textile")

Code:

proc print data=have noobs;
where &value_1 IN (&value_2);
var &_field;
run;

Log:

30   %put ####value_2 is: &value_2.;
####value_2 is: "Banking"|"Insurance"
31   
32   /*Filter and print the data*/
33           
34   proc print data=have noobs;
SYMBOLGEN:  Macro variable value_1 resolves to DIVISION
35   where &value_1 IN (&value_2);
SYMBOLGEN:  Macro variable value_2 resolves to "Banking"|"Insurance"
NOTE: Line generated by the macro variable "value_2".
35   "Banking"|"Insurance"
                               -
                               22
                               200
ERROR: Syntax error while parsing WHERE clause.

Re: Special characters in IN operator

A_Kh — Wed, 31 May 2023 18:08:21 GMT

While not sure about whether IN operator could accept the PIPE as a delimiter, I suggest using %scan function to separate pipe delimited values into individual values inside the parenthesis.

If macrovar has only 2 values then the following code would separate them into individual values to process using IN operator:

where &value_1 IN ("%scan(&value_2, 1, |)", "%scan(&value_2, 2, |)");

Re: Special characters in IN operator

David_Billa — Wed, 31 May 2023 18:18:00 GMT

@A_Kh I guess that your solution is close enough. How to make your code dynamic if macro variable has only 1 or 3 or 4 values?

Re: Special characters in IN operator

mkeintz — Wed, 31 May 2023 18:23:00 GMT

You can translate the "|" to a comma, making the macro expression amenable to the IN operator:

%let value2="Banking"|"Insurance";
%let cslist=%sysfunc(translate(&value2,%str(,),|));
%put &=cslist;
data want;
  set have; 
  where txt in (&cslist);
run;

Re: Special characters in IN operator

Tom — Wed, 31 May 2023 18:24:54 GMT

If the values do not contain | then use TRANSLATE() to convert the | into comma or space.

where &value_1 IN ( %sysfunc(translate(&value_2,%str( ),|)) );

To generate code like

where DIVISION in ("Banking" "Insurance");

Or use the FINDW() function instead of the IN operator.

So use something like this

where quote(trim(&value_1)),%sysfunc(quote(&value_2)),'|');

To generate code like:

where findw(quote(trim(DIVISION)),"""Banking""|""Insurance""",'|');

Re: Special characters in IN operator

David_Billa — Wed, 31 May 2023 18:30:22 GMT

@mkeintz @Tom I should not translate pipe to comma as there may be a value with comma between it (e.g. Firstname,Lastname) and it is valid.

Re: Special characters in IN operator

David_Billa — Wed, 31 May 2023 18:43:47 GMT

@mkeintz I tried this code and it yields only one record in the result instead of two.

%let value2="Bank,ing"|"Insurance";
%let cslist=%sysfunc(translate(&value2,%str(,),|));
%put &=cslist;
data have;
input name $;
datalines;
Bank,ing
Insurance
;
run;
data want;
  set have; 
  where name in (%sysfunc(translate(&value2,%str(,),|)));
run;

Log:

84   data have;
85   input name $;
86   datalines;
NOTE: The data set WORK.HAVE has 2 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
89   ;
90   run;
91   data want;
92     set have;
93     where name in (%sysfunc(translate(&value2,%str(,),|)));
94   run;
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE name='Bank,ing';
NOTE: The data set WORK.WANT has 1 observations and 1 variables.

Re: Special characters in IN operator

David_Billa — Wed, 31 May 2023 18:52:41 GMT

@Tom I used your instruction to replace pipe with comma and it is producing one record in the result instead of two.

%let value2="Bank,ing"|"Insurance";
%let cslist=%sysfunc(translate(&value2,%str(,),|));
%put &=cslist;
data have;
input name $;
datalines;
Bank,ing
Insurance
;
run;
data want;
  set have; 
  where name in ( %sysfunc(translate(&value2,%str(,),|)) );
run;

Re: Special characters in IN operator

A_Kh — Wed, 31 May 2023 19:02:41 GMT

You can create dynamic statement using COUNTC function and %do block. However, the code should be in macro definition:

Try this:

%macro print;
	proc print data=HAVE;
	where &value_1 IN (%do i= 1 %to (%sysfunc(countc(&value_2, |))+1);
						"%scan(&value_2, &i, |)" 
						%end;);
	run; 
%mend;

%print;

Re: Special characters in IN operator

Reeza — Wed, 31 May 2023 19:03:19 GMT

Your sample data isn't generating correctly (insurance has 9 characters default is 8).

If it generates correctly it works.

Re: Special characters in IN operator

Tom — Wed, 31 May 2023 19:46:02 GMT

@David_Billa wrote:

@mkeintz @Tom I should not translate pipe to comma as there may be a value with comma between it (e.g. Firstname,Lastname) and it is valid.

Not with the values of the macro variable you showed. In your example all of the values where enclosed in quotes.

Try it yourself WITHOUT ANY MACRO CODE.

So an expression like this

where divison in ("ABC,XYZ","QRX");

Or this

where divison in ("ABC,XYZ" "QRX");

Will find the observations where DIVISION is either QRX or ABC,XYZ. It will not find values of ABC or XYZ.