Missing single quote in macro generated code

[bheinsius]

Hi,

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.
[PRE]
%macro getValues(dsname,colname);
%let dsid=%sysfunc(open(&dsname));
%let vartype=%sysfunc(vartype(&dsid,%sysfunc(varnum(&dsid,&colname))));
%do %while(not(%sysfunc(fetch(&dsid))));
%if (&vartype=C) %then
%do;
%str(%')%sysfunc(compress(%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,&colname))))))%str(%')
%end;
%else
%do;
%sysfunc(compress(%sysfunc(getvarn(&dsid,%sysfunc(varnum(&dsid,&colname))))))
%end;
%end;
%let dsid=%sysfunc(close(&dsid));
%mend getValues;
[/PRE]
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'
[/PRE]
However, when I include the macro in a proc sql, it doesn't work:
[PRE]
15 proc sql;
16 create table x as
17 select *
18 from sashelp.class
19 where name in (%getValues(SASHELP.CLASS,NAME))

_
22
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, (, -, SELECT.


_
76
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
20 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
21 quit;
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
[/PRE]

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.

What am I doing wrong?

Thanks,
Bart

[data_null__]

I think you can get it working using the UNQUOTE function either in the macro or around the macro call.

I'm don't think your macro is a good idea. You can use select in the IN operator in SQL. That might be more general/efficient ...

[bheinsius]

The %UNQUOTE did the magic, thanks.
Can you explain why it solves the problem?

[data_null__]

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

[ArtC]

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.