Resolving macro variable inside PROC SQL

2
votes

I have a macro variable I need to use within PROC SQL. The way it resolves appears to have perfect syntax, but I am getting a syntax error and I'm not sure why;

%let test = mytext;
PROC SQL;
CREATE TABLE myTalbe&test AS
SELECT DISTINCT
    a.column
FROM
    tablename a
WHERE
    a.column = %bquote('&test')
;QUIT;

The error I get throws a red line under the resolved text, 'mytext', and says

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.

I don't feel like this error applies here. If I hard code in 'mytext' it works fine. Am I missing something right under my nose? Can anyone lend me a hand?

Thanks!!!

2
sas
We don't encourage use of enterprise-guide tag unless you're asking about EG functionality (as opposed to SAS language functionality). – Joe

2 Answers

3
votes

The macro quoting is confusing the SAS parser. For this program I would remove the use of %bquote() and just use double quotes instead of single quotes so that the macro variable reference will resolve.

WHERE a.column = "&test"

If your are actually generating pass thru SQL into a system that requires the use of single quotes for string literals then you will need to use %unquote() to remove the macro quoting.

... from connection to ... ( ...
WHERE a.column = %unquote(%bquote('&test'))
... ) ...
2
votes

The BQUOTE function tries to resolve the value immediately at execution time. Try removing it and using double quotes instead:

%let test = mytext;
PROC SQL;
CREATE TABLE myTalbe&test AS
SELECT DISTINCT
    a.column
FROM
    tablename a
WHERE
    a.column = "&test"
;QUIT;