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
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 );
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.
Ok, thanks! The single quotes helped when combined with another solution 😁
cics like %str(%')%qscan(%superq(acics),&i,' ')%str(%')
Also, when debugging macros, use the command
options mprint;
and then re-run the code, and show us the log from that run.
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 );
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;
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.
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';
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!
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.
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!
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.
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' )
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.