topic Re: Problem with functions of masking quotes. in SAS Programming

Problem with functions of masking quotes.

mariopellegrini — Thu, 17 Sep 2015 09:55:02 GMT

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;

Re: Problem with functions of masking quotes.

RW9 — Thu, 17 Sep 2015 10:39:16 GMT

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.

Re: Problem with functions of masking quotes.

ChrisNZ — Thu, 17 Sep 2015 12:10:28 GMT

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;

Re: Problem with functions of masking quotes.

ChrisNZ — Thu, 17 Sep 2015 12:15:25 GMT

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!

Re: Problem with functions of masking quotes.

Tom — Thu, 17 Sep 2015 14:34:05 GMT

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;

Re: Problem with functions of masking quotes.

mariopellegrini — Thu, 17 Sep 2015 14:19:00 GMT

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?

Re: Problem with functions of masking quotes.

Astounding — Thu, 17 Sep 2015 22:41:28 GMT

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.

Re: Problem with functions of masking quotes.

mariopellegrini — Fri, 18 Sep 2015 13:49:34 GMT

Thanks Chris_NewZealand!