I created the %getValues macro below, which should return all values of a column in a data set, within single quotes, separated by a space.
%if (&vartype=C) %then
It works when I %put it:
[PRE]15 %put %getValues(SASHELP.CLASS,NAME);
'Alfred' 'Alice' 'Barbara' 'Carol' 'Henry' 'James' 'Jane' 'Janet' 'Jeffrey' 'John' 'Joyce' 'Judy' 'Louise' 'Mary' 'Philip' 'Robert'
'Ronald' 'Thomas' 'William'
However, when I include the macro in a proc sql, it doesn't work:
15 proc sql;
16 create table x as
17 select *
18 from sashelp.class
19 where name in (%getValues(SASHELP.CLASS,NAME))
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, (, -, SELECT.
ERROR 76-322: Syntax error, statement will be ignored.
MPRINT(GETVALUES): 'Alfred' 'Alice' 'Barbara' 'Carol' 'Henry' 'James' 'Jane' 'Janet' 'Jeffrey' 'John' 'Joyce' 'Judy' 'Louise'
'Mary' 'Philip' 'Robert' 'Ronald' 'Thomas' 'William
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
In the log I see that the last quote is missing.
When I replace the %str(%') in the macro with double quotes or other characters, it works fine. But I need the single quotes to prevent macro resolution.
> The %UNQUOTE did the magic, thanks.
> Can you explain why it solves the problem?
Not technically. It's just that sometimes automatic unquoting doesn't work. Your program using %str(%') to create single quoted strings is classic example where automatic unquoting probably won't work.
It is a matter of timing. The %UNQUOTE forces the SQL parser to wait until the macro facility is completely finished. This is especially important when working with quoted strings. There have been some other fairly recent posts with related timing issues.