How to suppress macro resolution

[mikegia5]

I am passing a variable to a macro that will be used as a SQL LIKE predicate, so it can contain the % character, but I can't seem to get the macro to bypass the resolution attempt.  At best this is a warning message, but if the data actually matches a macro name, it will be an error. 

The variable contains a list of character values, separated by spaces, one or more of which could contain a %.  I first convert the % to an asterisks in an attempt to get around the macro resolution problem:

P*TZ P*T1 

The above value needs to be converted to 2 SQL LIKE predicates:

and (cics like "P%TZ"  or cics like "P%T1")

The variable name is ACICS.  Since I convert the % to * outside of the macro, I now have to convert it back to % in the macro (I've tried not converting as well).  Here's the macro code:

%if %length(&acics) > 1 %then
%do;
and (
%let acics=%sysfunc(translate(&acics,%NRSTR(%_),%NRSTR(*+)));
%do i=1 %to %sysfunc(countw(%superq(acics),' '));
%if &i > 1 %then or;
CICS like "%scan(%superq(acics),&i,' ')"
%end;) %end;

I get these messages with MLOGIC and SYMBOLGEN enabled showing the warnings:

MLOGIC(FRITZ): %IF condition %length(&acics) > 1 is TRUE
MLOGIC(FRITZ): %LET (variable name is ACICS)
SYMBOLGEN: Macro variable ACICS resolves to P*TZ P*T1
WARNING: Apparent invocation of macro TZ not resolved.
WARNING: Apparent invocation of macro T1 not resolved.

MLOGIC(FRITZ): %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(FRITZ): %IF condition &i > 1 is FALSE
SYMBOLGEN: Macro variable I resolves to 1
WARNING: Apparent invocation of macro TZ not resolved.
MLOGIC(FRITZ): %DO loop index variable I is now 2; loop will iterate again.
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(FRITZ): %IF condition &i > 1 is TRUE
SYMBOLGEN: Macro variable I resolves to 2
WARNING: Apparent invocation of macro T1 not resolved.
MLOGIC(FRITZ): %DO loop index variable I is now 3; loop will not iterate again.

The generated code comes out good, but, I still get the warnings:

and ( cics like "P%TZ" or cics like "P%T1") ;   

Does anyone have any suggestions on how to stop the macro engine from trying to resolve the macro references?

Thank you

SAS 9.4

[Reeza]

Use single quotes, if possible, or switch to FIND() or regular expressions are other options.

[mikegia5]

Thank you Reeza,

I'm not sure how to apply your suggestions?  I changed the double quotes to single quotes but that didn't work.  Are you suggesting I don't use the SQL LIKE predicate and instead use a FIND or REGEX to search through the database? While that might work, the database I'm reading is in a relational database (DB2) that contains 10 billion rows and anything that isn't supported in the database will result in the data being passed back to SAS, so I really want to use native relational database predicates as much as possible.

[Reeza]

Without seeing more of the code I can't answer. I don't know how you're passing parameters or using the values. DB2 does support regex from what I can see.

https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/do...

[mikegia5]

Ok, thanks!  The single quotes helped when combined with another solution 😁

cics like %str(%')%qscan(%superq(acics),&i,' ')%str(%') 

[PaigeMiller]

Also, when debugging macros, use the command

options mprint;

and then re-run the code, and show us the log from that run.

[CurtisMackWSIPP · Accepted solution]

Try using the "q" versions of sysfunc and scan.  They work the same as the normal functions but macro quote the results.

%macro test(acics);
  %if %length(&acics) > 1 %then
  %do;
  and (
  %let acics=%qsysfunc(translate(&acics,%NRSTR(%_),%NRSTR(*+)));
  %do i=1 %to %sysfunc(countw(%superq(acics),' '));
  %if &i > 1 %then or;
  CICS like "%qscan(%superq(acics),&i,' ')"
  %end;) %end;
%mend;
options mprint;
%test(P*TZ P*T1 );

[mikegia5]

Perfect - thanks for understanding what I was trying to do and coming up with a solution!  I changed to qsysfunc and qscan and that solved the warnings coming out of the macro, but what I didn't say is that this is dynamic code and I run the macro via the execute function so when it came time to run the code, I still had some warnings using double quotes (i.e., LIKE "P%TZ").  So I added an escaped single quote and now it runs without a problem!  Thanks again.

%if %length(&acics) > 1 %then                                   
%do;                                                            
and (                                                           
%let acics=%qsysfunc(translate(&acics,%NRSTR(%_),%NRSTR(*+)));  
%do i=1 %to %sysfunc(countw(%superq(acics),' '));               
  %if &i > 1 %then or;                                          
  region_id like %str(%')%qscan(%superq(acics),&i,' ')%str(%')  
%end;) %end;                                                    

[mikegia5]

Thanks - I'm already using mprint but this is a large macro (about 400 lines) and the resulting MPRINT is long too, so I just snipped the relevant part.

[data_null__]

I think you can use NRSTR for that but you need to escape the % sign in the expression %%

183  options symbolgen=1;
184  data c;
185     input cics $;
186     put _all_;
187     cards;

cics=PthiTZ _ERROR_=0 _N_=1
cics=XthiT1 _ERROR_=0 _N_=2
cics=P--T1 _ERROR_=0 _N_=3
NOTE: The data set WORK.C has 3 observations and 1 variables.
191  ;;;;
192
193  %let a = %nrstr(cics like "P%%TZ" or cics like "P%%T1");
194
195  data _null_;
196     set c;
SYMBOLGEN:  Macro variable A resolves to cics like "P%TZ" or cics like "P%T1"
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for
            printing.
197     where &a;
198     put _all_;
199     run;

cics=PthiTZ _ERROR_=0 _N_=1
cics=P--T1 _ERROR_=0 _N_=2
NOTE: There were 2 observations read from the data set WORK.C.
      WHERE cics like 'P%TZ' or cics like 'P%T1';

[mikegia5]

I don't have any control over the input data, so I would need to scan for the % sign and double up on it.  I found another solution above that worked.  Thanks for your input!

[ScottBass]

So it looks like you've got a macro variable/parameter containing a list of where clause predicate suffixes, i.e. TZ T1 etc.

And you're trying to build a where clause, looping over those tokens:

cics like 'P%TZ' or cics like 'P%T1'

My macro library is located at https://github.com/scottbass/SAS/tree/master/Macro. 

You want %loop, %squote, and %parmv.  If you don't want to use them, then use the code as a guide for your own macro.

I would pull the AND operator out of the macro, so the macro contains just the loop construct, with the calling code containing the rest of the where clause syntax.  But that is just coding style.

Finally, the simplest approach is just to temporarily set option nomerror to suppress warnings of unresolved macro references.  Of course, don't create %TZ or %T1 macros 😉

The code then becomes:

options mprint nomerror;

%macro code;
%if (&__iter__ gt 1) %then or;
cics like %squote(P%&word)
%mend;

%let asics=TZ T1;

data foo;
   set sashelp.class (rename=(name=cics));
   where 1=1 and (%loop(&asics));
run;

* Alternative approach ;
%let where=1=1 and (%loop(&asics));

data foo;
   set sashelp.class (rename=(name=cics));
   where &where;
run;

and the log:

29         %macro code;
30         %if (&__iter__ gt 1) %then or;
31         cics like %squote(P%&word)
32         %mend;
33         
34         %let asics=TZ T1;
35         
36         data foo;
37            set sashelp.class (rename=(name=cics));
38            where 1=1 and (%loop(&asics));
MPRINT(CODE):   cics like
MPRINT(SQUOTE):   'P%TZ'
MPRINT(CODE):   or cics like
MPRINT(SQUOTE):   'P%T1'
39         run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
      WHERE (1=1) and (cics like 'P%TZ' or cics like 'P%T1');
2 The SAS System                                                                        08:18 Thursday, October 15, 2020

NOTE: The data set WORK.FOO has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              269.75k
      OS Memory           13092.00k
      Timestamp           15/10/2020 08:42:10 AM
      

40         
41         * Alternative approach ;
42         %let where=1=1 and (%loop(&asics));
43         
44         data foo;
45            set sashelp.class (rename=(name=cics));
46            where &where;
47         run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
      WHERE (1=1) and (cics like 'P%TZ' or cics like 'P%T1');
NOTE: The data set WORK.FOO has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              269.62k
      OS Memory           13092.00k
      Timestamp           15/10/2020 08:42:10 AM

Of course, this returns nothing using sashelp.class, but should be syntactically correct for your scenario.

If this does not meet your needs, please give details why so we can help further.

[mikegia5]

Thank you Scott.  I bookmarked your GitHub link and will definitely be taking a look.  For now I've solved it using the Qscan, qsysfunc, and single quoting techniques above.  If they didn't work I would have worked my way down to your solution next.  I didn't want to use nomerror since this is just data and someone will run a combination resulting in finding a real macro name!

[ScottBass]

Thanks Mike.  I also realized after posting that my code doesn't account for a null &asics parameter, and would generate a syntactically incorrect where clause (my coding style changes). 

Since you have a solution, I don't consider it worthwhile to modify my post.

Glad you got it working.

[Tom]

Note that to the macro process quotes are just like other characters. So using ' ' as the delimiters in your %SCAN() call means that both single quotes and spaces are delimiters. 

But quotes do have an impact: macro triggers (& and %) are ignored inside of single quotes.  You can use that fact to simplify the translation operations a little.

Let's make a macro that does what it looks like you are trying to do, generate a LIKE ANY type syntax.

%macro likeany(var,terms);
%local i sep term;
(
%do i=1 %to %sysfunc(countw(%superq(terms),%str( )));
  %let term=%str(%')%qscan(%superq(terms),&i,%str( ))%str(%');
  &sep &var like %sysfunc(translate(&term,'%_','*+'))
  %let sep=or;
%end;
)
%mend likeany;

Now let's test it :

123   %put %likeany(cics,P*TZ P*T1);
( cics like 'P%TZ'   or cics like 'P%T1' )