data logStream;
strId=1;strline="xxxxMyJob XX XXX X bbbbbbb aaaaaaa cccccccc";output;
strId=2;strline="https//:xxxx aaaMYJOB XX XXX X";output;
strId=3;strline="xxxx jobstorun XX XXX X";output;
strId=4;strline="xxxx jobstorun myjobXX XXX Xrp";output;
strId=5;strline="https//:xxxx yuiuyhiuo XX XXX X";output;
run;
%let webService=%upcase(myJob);
/* Love the LIKE functionality with wildcard "%" ===> "%string%" */
/*However I would like to remove the hardcoding MYJOB to be replaced by the macro variable <webService>.
I have tried all the quoting functions(%str, %nrstr,%unquote,%bquote, %quote, %nrquote, etc..." unsucessfully. Definitively I don't understand them.
Please help and correct the commented line below.*/
proc sql;
create table work.lines as
select t1.*
from work.logStream t1
where upcase(strline) like "%MYJOB%" and NOT upcase(strline) like upcase("%https%") /*works */
/* where upcase(strline) like "%&webService%" and NOT upcase(strline) like upcase("%https%") */ /* help */
;
quit;
/* Result expected: row 1 and 4 */
%NRSTR() works pretty well. Double up the %'s in the string.
%BQUOTE() also helps since you can then add single quotes around the value.
1 %let x=%bquote('%nrstr(%%)abc'); 2 %put &x; '%abc'
For your particular use case just don't use LIKE. CONTAINS will test for a substring.
where upcase(strline) contains %upcase("&webService")
and NOT (upcase(strline) contains 'HTTPS')
Likely overthinking things a bit. There are other search functions besides LIKE that can completely avoid the use of any macro triggers you get from the %
See if this is what you want:
proc sql; create table work.lines as select t1.* from work.logStream t1 where index(upcase(strline),"&webService")>0 and index( upcase(strline),"HTTPS")=0 ; quit;
The index function return the position in a string of the second parameter in the first. So index=0 is "not found".
%NRSTR() works pretty well. Double up the %'s in the string.
%BQUOTE() also helps since you can then add single quotes around the value.
1 %let x=%bquote('%nrstr(%%)abc'); 2 %put &x; '%abc'
For your particular use case just don't use LIKE. CONTAINS will test for a substring.
where upcase(strline) contains %upcase("&webService")
and NOT (upcase(strline) contains 'HTTPS')
Thank you all @Tom , @ballardw and @Patrick . All solutions were great.
I learned different ways!!!
I had to select one "Accepted solution" and couldn't decided....all so nicely written.
However since @Tom had provided an example with %bquote and %nrstr, I chose that solution.
Everything I tried with bquote, I didn't understand how to use it.
One of my bad attempts example 🙄
where upcase(strline) like "%bquote(%&webService%)" ....
But thank you very much. Very helpful.
Below two options when using the like operator
data logStream;
infile datalines truncover dlm='|';
input strID strline:$60.;
datalines;
1|xxxxMyJob XX XXX X bbbbbbb aaaaaaa cccccccc
2|https//:xxxx aaaMYJOB XX XXX X
3|xxxx jobstorun XX XXX X
4|xxxx jobstorun myjobXX XXX Xrp
5|https//:xxxx yuiuyhiuo XX XXX X
;
/* option 1 */
%let mvar=myJob;
proc sql;
/* create table work.lines as */
select t1.*
from work.logStream t1
where upcase(strline) like cats('%',upcase("&mvar"),'%')
;
quit;
/* option 2 */
%let mvar=myJob;
data _null_;
call symputx('mvar',cats('%',"&mvar",'%'));
run;
proc sql;
/* create table work.lines as */
select t1.*
from work.logStream t1
where upcase(strline) like upcase(symget("mvar"))
;
quit;
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.