'a' vs. %str(%')a%str(%')

[yuguo]

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?%  

[Astounding · Accepted solution]

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.

[FriedEgg]

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;