BookmarkSubscribeRSS Feed
bheinsius
Lapis Lazuli | Level 10
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
4 REPLIES 4
data_null__
Jade | Level 19
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
Lapis Lazuli | Level 10
The %UNQUOTE did the magic, thanks.
Can you explain why it solves the problem?
data_null__
Jade | Level 19
> 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
Rhodochrosite | Level 12
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 810 views
  • 0 likes
  • 3 in conversation