BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

mariopellegrini
Quartz | Level 8

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?

ChrisNZ
Tourmaline | Level 20

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;

 

ChrisNZ
Tourmaline | Level 20

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!

Tom
Super User Tom
Super User

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;

 

Astounding
PROC Star

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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