Macro variable quoting error with SAS

Question

I am trying to insert a row into a SAS data set using PROC SQL and the values of existing macro variables but I'm getting the standard syntax error message. Here is an example of the code that fails:

%let viewname=MKTVIEWS.imei_ref;
%let xrc=0;
%let xmsg= ;

proc sql;
   create table results (viewname char(40), xrc numeric, xmsg char(200));
   insert into results (viewname, xrc, xmsg)
   values ( %str(%')&viewname%str(%') 
          , &xrc 
          , %str(%')%superq(xmsg)%str(%') );
quit;

Here is the error message:

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, 
a numeric constant, a datetime constant, a missing value, +, -, MISSING, NULL,
USER.

Running the program without the two character macro variables works fine:

proc sql;
   create table results (viewname char(40), xrc numeric, xmsg char(200));
   insert into results (viewname, xrc, xmsg)
   values ( 'MKTVIEWS.imei_ref'
          , &xrc 
          , '' );
quit;

Clearly I'm missing something about macro quoting or something similar. I even tried using temporary macro variables rather than embedding those %STR calls to create a quoted string, but that didn't work either.

Sometimes I've noticed SAS gets tripped up by the valid use of macro quoting functions. When this happens, wrapping the troublesome code in a %unquote() will allow the parser to continue. — Robert Penridge
– Robert Penridge, Commented Jun 27, 2013 at 13:35

Michael Richardson · Accepted Answer · 2013-06-26 19:51:00Z

3

Maybe I'm missing something, but wouldn't "&viewname" do the job?

edited Jun 26, 2013 at 19:51

answered Jun 26, 2013 at 19:42

Michael Richardson

4,3022 gold badges34 silver badges51 bronze badges

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

1 Comment

BellevueBob Over a year ago

Arrgh, yes. Overlooking the obvious I am. Still, I'm curious why it doesn't work as written but I don't really care at this point. Thanks much.

Joe · Accepted Answer · 2013-06-26 19:34:24Z

1

I find it easiest to use datastep quoting functions, largely because I'm terrible at real macro quoting.

%let viewname=MKTVIEWS.imei_ref;
%let xrc=0;
%let xmsg= ;


options symbolgen mprint;
proc sql;
   create table results (viewname char(40), xrc numeric, xmsg char(200));
   insert into results (viewname, xrc, xmsg)
   values ( %sysfunc(quote(&viewname))
          , &xrc 
          , %sysfunc(quote(%superq(xmsg))) );
quit;

Does that accomplish what you're hoping?

answered Jun 26, 2013 at 19:34

Joe

63.5k7 gold badges51 silver badges70 bronze badges

2 Comments

Robert Penridge Over a year ago

Haha don't worry Joe - I don't think there's anyone who is actually GOOD at real macro quoting.

Michael Kersten Over a year ago

Ian Whitlock is pretty good, but even he states in the conclusion of "A Serious Look Macro Quoting" : "Bugs in the macro facility prevent recognition of a consistent pattern of how macro quoting works." and gives other reasons why macro quoting is so hard to learn/understand. His paper can be found at www2.sas.com/proceedings/sugi28/011-28.pdf

vasja · Accepted Answer · 2013-06-27 21:25:51Z

Bob, it doesn't work, because it works. %STR masked quotes not just from macro compiler (as you expected it), but also from the program compiler in general. So in your SQL you provided value for character variable that didn't had quotes.

See the difference here, no macro stuff except of %str:

data text;
length text $50;
text=%str(%')bla bla text%str(%');
run;

data text;
length text $50;
text="%str(%')bla bla text%str(%')";
run;

In case you needed quoted strings in your table:

proc sql;
   create table results (viewname char(40), xrc numeric, xmsg char(200));
   insert into results (viewname, xrc, xmsg)
   values ( "%str(%')&viewname%str(%')"
          , &xrc 
          , "%str(%')%superq(xmsg)%str(%')" );
quit;

Of course, as already said, nobody's really good in macro quoting :-)

Collectives™ on Stack Overflow

Macro variable quoting error with SAS

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related