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;
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;
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.
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?
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;
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!
Thanks Chris_NewZealan
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;
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.
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.
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.