Desktop productivity for business analysts and programmers

Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

On Enterprise Guide 7.1, I encountered a syntax error related to quotes in a macro parameter. An abbreviated version of the code in question follows:

 

proc sql;
create table testtable1 as
select * 
from in1.eligibility
where 201501<=monthid<=201706 
&whereavars.;
quit;

I then created a user prompt for whereavars and entered the following text:

and avar4="STAR"

This produced a syntax error when I tried to run the SQL query above.

I know the syntax error is related to the quotes because I changed the prompt text to and 1 and the SQL query ran without issue. I tried single quotes instead of double and got the same result.

I even tried creating the macro variable in the program instead of the prompt. This query worked, too. So this has something to do with the quotes and the prompt.

 

Any ideas?


Accepted Solutions
Solution
‎09-11-2017 11:36 AM
Super User
Super User
Posts: 6,842

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

SAS Enterpise Guide and SAS/Studio normally use %BQUOTE() when creating the macro variables.  There should be options you can change that will show the generated code in the SAS log.

%let where = %bquote(and name='Alfred');

It is this macro quoting that is causing your problem. You can use %unquote() to remove the macro quoting.

proc sql;
create table test1 as
  select * 
  from sashelp.class
  where 1=1 %unquote(&where)
;
quit;

Without removing the macro quoting the SAS parser is not seeing the quotes as indicating a string literal and so it causes syntax errors.

90    proc sql ;
91    create table test2 as
92      select *
93      from sashelp.class
94      where 1=1 &where
95    ;
NOTE: Line generated by the macro variable "WHERE".
1      and name='Alfred'
                -
                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.

96    quit;

View solution in original post


All Replies
Highlighted
Super User
Posts: 11,107

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

What is the error? Please paste the code with the error from the log into a code box using the forum {i} menu icon.

 

One thing to do is to run the code with

 

options mprint;

Then the code generated by the macro variable should show in the log. it may appear a bit differently than you think.

Respected Advisor
Posts: 4,131

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

[ Edited ]

@adkilmer

If you investigate your SAS log you'll find something like:

%LET WHEREAVARS = and avar4=%nrstr(%")STAR(%");

Use an %unquote() function as below and things will work.

proc sql;
  create table testtable1 as
    select * 
    from in1.eligibility
    where 201501<=monthid<=201706 
        %unquote(&whereavars.);
quit;

 

PROC Star
Posts: 1,146

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

No ideas, just bafflement!

 

I can reproduce your situation exactly. I'm evenly divided between whether it's a bug, or some arcane rule in macro variable substitution that I don't know.

 

Given that a bug is a possibility, if you don't get an answer on this communitiy in a reasonable amount of time, I would have no qualms about submitting this to SAS tech. support. If  you've never done it before, don't be apprehensive, they're a great group.

 

To test on my machine, I modified your example a bit. Try this out in your environment, and if it fails successfully (hah!) for you, you can send it to tech support as they'll be able to try it right out of the box.

 

And for heaven's sake report back. This is driving me nuts!

 

Tom

 

proc sql;
create table testtable1 as
select * 
from sashelp.class
where 13<=age<=14
&whereavars.;
quit;
/* Set prompt to and name = "Alice" */
Respected Advisor
Posts: 4,131

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

@TomKari

See my post. The prompt adds macro quoting for the the double quotes. An %unquote() resolves the issue.

PROC Star
Posts: 1,146

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

@Patrick Very cool! Thanks. I'm far from an expert on macro quoting, so I was hoping someone more knowledgeable than me would come to the rescue. Now this can quit bugging me, and I'll know for the future!

 

Tom

Solution
‎09-11-2017 11:36 AM
Super User
Super User
Posts: 6,842

Re: Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

SAS Enterpise Guide and SAS/Studio normally use %BQUOTE() when creating the macro variables.  There should be options you can change that will show the generated code in the SAS log.

%let where = %bquote(and name='Alfred');

It is this macro quoting that is causing your problem. You can use %unquote() to remove the macro quoting.

proc sql;
create table test1 as
  select * 
  from sashelp.class
  where 1=1 %unquote(&where)
;
quit;

Without removing the macro quoting the SAS parser is not seeing the quotes as indicating a string literal and so it causes syntax errors.

90    proc sql ;
91    create table test2 as
92      select *
93      from sashelp.class
94      where 1=1 &where
95    ;
NOTE: Line generated by the macro variable "WHERE".
1      and name='Alfred'
                -
                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.

96    quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 363 views
  • 6 likes
  • 5 in conversation