Resolving macro variable inside PROC SQL

Question

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

We don't encourage use of enterprise-guide tag unless you're asking about EG functionality (as opposed to SAS language functionality). — Joe
– Joe, Commented Jun 5, 2017 at 16:10

Tom · Accepted Answer · 2017-06-05 15:58:23Z

3

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

answered Jun 5, 2017 at 15:58

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joshua Schlichting Over a year ago

I always use %bquote without fail when I have pass-throughs. I would have swore I tried double quotes first and I had issues, before posting this issue. I gotta give it to Allan though, since he answered first! Thanks, Tom!

Allan Bowe · Accepted Answer · 2017-06-05 15:32:48Z

2

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;

edited Jun 5, 2017 at 15:32

answered Jun 5, 2017 at 15:26

Allan Bowe

12.8k20 gold badges83 silver badges126 bronze badges

4 Comments

Joshua Schlichting Over a year ago

This generates the same error, this time showing my desired output with double quotes

Joshua Schlichting Over a year ago

NOTE: Line generated by the macro function "BQUOTE". 77 "mytext" This shows up before the error

Allan Bowe Over a year ago

apologies yes - bquote is a funny one! Answer updated

Joshua Schlichting Over a year ago

I would have swore that I had already done this, and it didn't work. This use of just double quotes appears to be giving me the desired output. Thanks, Allan.

Collectives™ on Stack Overflow

Resolving macro variable inside PROC SQL

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related