Special characters in IN operator

[David_Billa]

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.

[A_Kh]

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, |)");

[David_Billa]

@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?

[A_Kh]

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;

[mkeintz]

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;

[David_Billa]

@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.

[Tom]

---

@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.

[David_Billa]

@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.

[Tom]

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""",'|');

[David_Billa]

@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;

[Reeza]

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

If it generates correctly it works.