topic Re: What quoting function to use? Tried everything and not getting result expected in SAS Programming

What quoting function to use? Tried everything and not getting result expected

PopCorn14 — Wed, 24 Jan 2024 20:51:19 GMT

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 */

Re: What quoting function to use? Tried everything and not getting result expected

ballardw — Wed, 24 Jan 2024 21:04:52 GMT

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

Re: What quoting function to use? Tried everything and not getting result expected

Tom — Wed, 24 Jan 2024 21:13:11 GMT

%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')

Re: What quoting function to use? Tried everything and not getting result expected

Patrick — Thu, 25 Jan 2024 01:32:04 GMT

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;

Re: What quoting function to use? Tried everything and not getting result expected

PopCorn14 — Thu, 25 Jan 2024 17:20:00 GMT

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.

Re: What quoting function to use? Tried everything and not getting result expected

PopCorn14 — Thu, 25 Jan 2024 17:22:47 GMT

Thank you @ballardw.
Love the index function.

Re: What quoting function to use? Tried everything and not getting result expected

PopCorn14 — Thu, 25 Jan 2024 17:27:13 GMT

Thank you @Patrick for showing 2 options. I like the fact that you gave me an opportunity to still use the LIKE by using the CATS for the "%" which I was really wanted to use. Also learned how to use the symget. Cool.