Syntax Error: Quotes in Macro Parameter Generated by EG Prompt

[adkilmer]

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?

[ballardw]

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.

[Patrick]

@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;

[TomKari]

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" */

[Patrick]

@TomKari

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

[TomKari]

@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

[Tom · Accepted solution]

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;