DATA Step, Macro, Functions and more

Problem with functions of masking quotes.

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Problem with functions of masking quotes.

I have a problem with the macro variables using the functions of masking quotes. In the first example, using 'call symput' macro variable 'x' is created and printed correctly, but then at run a proc sql, the use of this macro variable creates an error.

In the second example they are made to the same things, with the addition of call symput to create the macro again variable, then the execution of proc sql does not create errors.

I do not understand why it was necessary to call symput, when printing the macro variable before gave a correct result. I do not understand why the first example it faults.

 

/*** datasets starting ***/

data test;

length note $ 10.;

note = "test1";output;

note = "test2";output;

note = "test3";output;

note = "no_present1";output;

note = "no_present2";output;

run;

 

 

/*** first example:*/

%let testo1 =  test;

%let x = %nrstr(%')%str(%%)%cmpres(&testo1)%str(%%%');

OPTIONS MPRINT;

%macro ex_1();

      proc sql;

      create table sel2 as

      select * from test

      where note like &x;

      quit;

%mend;

%ex_1;

 

/*** second example ***/

%let testo1 =  test;

%let x = %nrstr(%')%str(%%)%cmpres(&testo1)%str(%%%');

data _null_;

x="&x";

call symput ('x',x);

run;

OPTIONS MPRINT;

%macro ex_2();

      proc sql;

      create table sel2 as

      select * from test

      where note like &x;

      quit;

%mend;

%ex_2;


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
PROC Star
Posts: 1,561

Re: Problem with functions of masking quotes.

You need to unquote your value. You can avoid the call symput like this:

 

 

/*** first example:*/

%let testo1 = test;

%let x = %unquote(%nrstr(%'%%)&testo1%str(%%%'));

OPTIONS MPRINT;

%macro ex_1();

     proc sql;

     create table sel2 as

     select * from test

     where note like &x;

     quit;

%mend;

%ex_1;

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,400

Re: Problem with functions of masking quotes.

Hi,

 

I have to say, my opinion is that as soon as there is more than one % in a line of code, then there is a strong possiblity of doing things another way and in simple syntax.  E.g.

%let testo1 =  test;

data _null_;

  call execute("proc sql;

                         create table sel2 as

                         select * from test

                         where note like '"||strip(&x)||"';

                       quit;");

run;

 

There are plenty of other ways of coding these things, why do you need to go through all that %nrstr(%')%str(%%)%cmpres(&testo1)%str(%%%');

just to use macro facility?  If you supply the problem, maybe other solutions can be provided.

Contributor
Posts: 33

Re: Problem with functions of masking quotes.

[ Edited ]

Thanks RW9.

I need to go through all that nrstr% (% ')% str (%%)% cmpres (and text1)% str (%%%')
because I need to write a variable text between the characters '% and %' to use like in proc sql.

Do you know other ways?

Solution
‎09-25-2015 06:23 AM
PROC Star
Posts: 1,561

Re: Problem with functions of masking quotes.

You need to unquote your value. You can avoid the call symput like this:

 

 

/*** first example:*/

%let testo1 = test;

%let x = %unquote(%nrstr(%'%%)&testo1%str(%%%'));

OPTIONS MPRINT;

%macro ex_1();

     proc sql;

     create table sel2 as

     select * from test

     where note like &x;

     quit;

%mend;

%ex_1;

 

PROC Star
Posts: 1,561

Re: Problem with functions of masking quotes.

Boy I really dislike the new forum layout!

 

Not much fits on the screen anymore as it is very sparse, the banner at the top wastes even more space. It is ridiculously wide. The top half of the screen is polluted before we can see any content.

And now I see that the tedious paste to MS word to keep the code font/colors no longer works.

So much for progress!

Contributor
Posts: 33

Re: Problem with functions of masking quotes.

Thanks 

Super User
Super User
Posts: 6,499

Re: Problem with functions of masking quotes.

[ Edited ]

To me it looks like you are trying to add beginning and ending % wildcard characters to the value of macro variable to use in the SQL LIKE clause.  It is much easier if you just use double quotes and * in place of single quotes and % and then convert them later using something like the TRANSLATE() function.  Here is an example:

 

%let mvar=fr ;
%let like_value=%sysfunc(translate("*&mvar*",'%',"*"));
%Put &=like_value;
proc sql ;
   select * from sashelp.class where name like &like_value ;
quit;

 

Super User
Posts: 5,081

Re: Problem with functions of masking quotes.

It's generally easier to get these complex strings into a macro variable using a DATA step.  For example:

 

%let test01 = test;

data _null_;

call symput('x', ' ''%' || "&test01" || '%'' ');

run;

 

Then you should be able to refer to &x in the SQL code. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 325 views
  • 0 likes
  • 5 in conversation