CODEWITHSUNDEEP
sas
Joshua Schlichting
Joshua Schlichting

Reputation: 3460

Resolving macro variable inside PROC SQL

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!!!

Upvotes: 2

Views: 4835

Answers (2)

Tom
Tom

Reputation: 51621

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'))
... ) ...

Upvotes: 3

Allan Bowe
Allan Bowe

Reputation: 12701

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;

Upvotes: 2

Related Questions