This is largely a theoretical question about macro variables. I have two macro variables, v1 and v2. Both contain 3 characters consisting of 'a' (e.g. a single quote, the lowercase letter a, and the ending single quote). If you test their values, you'll find that they are equal:
dm log 'clear'; dm output 'clear';
option symbolgen mlogic mprint;
%macro myMacro;
%let v1='a';
%let v2=%str(%')a%str(%');
%if &v1=&v2 %then %put 1; %else %put 0;
%mend;
%myMacro
Log:
1 dm log 'clear'; dm output 'clear';
2 option symbolgen mlogic mprint;
3
4 %macro myMacro;
5 %let v1='a';
6 %let v2=%str(%')a%str(%');
7 %if &v1=&v2 %then %put 1; %else %put 0;
8 %mend;
9 %myMacro
MLOGIC(MYMACRO): Beginning execution.
MLOGIC(MYMACRO): %LET (variable name is V1)
MLOGIC(MYMACRO): %LET (variable name is V2)
SYMBOLGEN: Macro variable V1 resolves to 'a'
SYMBOLGEN: Macro variable V2 resolves to 'a'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
MLOGIC(MYMACRO): %IF condition &v1=&v2 is TRUE
MLOGIC(MYMACRO): %PUT 1
1
MLOGIC(MYMACRO): Ending execution.
However, these 2 macro variables are different as illustrated in the next program:
data test;
x='a'; y=1; output;
x='b'; y=2; output;
run;
/*macro - this works*/
%let v1='a';
proc sql;
select * from test
where x=&v1;
quit;
/*macro2 - this doesn't work*/
%let v2=%str(%')a%str(%');
proc sql;
select * from test
where x=&v2;
quit;
%put _all_;
This is the log:
20 data test;
21 x='a'; y=1; output;
22 x='b'; y=2; output;
23 run;
NOTE: The data set WORK.TEST has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds
24
25 /*macro - this works*/
26 %let v1='a';
27 proc sql;
28 select * from test
29 where x=&v1;
SYMBOLGEN: Macro variable V1 resolves to 'a'
30 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.06 seconds
31
32 /*macro2 - this doesn't work*/
33 %let v2=%str(%')a%str(%');
34 proc sql;
35 select * from test
36 where x=&v2;
SYMBOLGEN: Macro variable V2 resolves to 'a'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been
unquoted for printing.
NOTE: Line generated by the macro variable "V2".
1 'a'
-
22
-
200
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY,
BTRIM, CALCULATED, CASE, INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER.
ERROR 200-322: The symbol is not recognized and will be ignored.
37 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
38
39 %put _all_;
GLOBAL SQLOBS 0
GLOBAL V2 a
GLOBAL SQLOOPS 0
GLOBAL SYS_SQL_IP_ALL -1
GLOBAL SYS_SQL_IP_STMT
GLOBAL SQLXOBS 0
GLOBAL SQLRC 8
GLOBAL SQLEXITCODE 0
GLOBAL V1 'a'
AUTOMATIC AFDSID 0
AUTOMATIC AFDSNAME
In the log generated by %put _all_, the macro variable v2 has some garbage (e.g. delta characters).
Here's is my question. The value of the macro variable v2 is actually different from 'a'. This is a deliberate design by SAS or a bug?%
SQL is notorious for failing to unquote characters in time to parse them correctly. The most common application is passing a password to SQL, but the solution is straightforward in any event:
where x=%unquote(&v2);
That should handle the problem.
SQL is notorious for failing to unquote characters in time to parse them correctly. The most common application is passing a password to SQL, but the solution is straightforward in any event:
where x=%unquote(&v2);
That should handle the problem.
This is deliberate and a effect of the use of macro-quoting.
%let m=%str(%')M%str(%');
proc sql;
/* will not work */ select count(*) from sashelp.class where sex=&m.;
/* will work */ select count(*) from sashelp.class where sex=%unquote(&m.);
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.