SAS macro variable quotes

0
votes

I have a macro variable 

%let data =london paris;

The real variable is made by a sql into statment and will vary. What I want to do is to make a new macro variable datalist, which looks like this:

('london' , 'paris')

I am going to use this in where statment (old is a table which contains a city variable):

data new;
   set old;
   where city in &datalist;
run;

I have tried to make this variable in the following ugly way:

%let data =london paris;
%let datalist =  (%str(%')%sysfunc(tranwrd(&data,%str( ),%str(%' ,    %')))%str(%')); 
%put &datalist;

But when I run this code I got the following error message:

10   %let data =london paris;
11   %let datalist =  (%str(%')%sysfunc(tranwrd(&data,%str( ),%str(%' , %')))%str(%'));
NOTE: Line generated by the macro function "SYSFUNC".
1    london' , 'paris
           -----
           49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.
12   %put &datalist;
NOTE: Line generated by the macro variable "DATALIST".
1     ('london' , 'paris')
              -----
              49
('london' , 'paris')
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

What is really strange is that I can use this variable if I put in in a %unqote:

data new;
   set old;
   where city in %unquote(&datalist);
run;

But I still have this error message. Is there a better way of change the array variable from to:

paris london -> ('paris' , 'london')

SOLUTION The simple solution proposed by Quentin actually solved my problem

where findw("&datalist",trim(city)) ;

But the macro Quentin proposed also worked find but since I do not have any spaces in my variables (it is not cities in my dataset but sas column names) this simple solution is good enough.

3
sassas-macro
If you can back up several steps, you can add the QUOTE() and separated by in your SQL to create the macro variable in that way in the first place. Is that an option at all? – Reeza
Will QUOTE actually do that though, or does it use " instead? (I assume this is something that goes into a passthrough SQL query eventually) – Joe
That said, certainly could use cats("'",cityname,"'") instead if I'm right about quote. – Joe
DId you try using %Qsysfunc()? Or does any macro quoting cause issues. – Tom
Joe. This looks good. I will try it but need to read more about macro quoting in order to understand this whole issue. – fossekall

3 Answers

2
votes

I'm a big fan of Richard DeVenezia's %seplist utility macro: http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist. I revised it in my personal library to make the last statement %unquote(&emit) rather than &emit. With that, you could code:

where city IN (%seplist(&datalist,nest=Q)) ;

The nest=Q adds single quotes around each item in the list.

That said, another alternative is to use FINDW, which gets you out needing to quote each item, i.e.:

where findw("&datalist",trim(city)) ;
1
votes

Your %str requires unquoting, for one. I would not use translate or similar; instead make this a macro and loop over the words.

%let data =Jane Alfred;

%macro quote_data(var=);

  %do i = 1 %to %sysfunc(countw(&var.));  %unquote(%str(%')%scan(&var.,&i.)%str(%'))
  %end;

%mend quote_data;

%let dataquote = %quote_data(var=&data.);
%put &=dataquote.;

proc sql;
select * from sashelp.class 
where name in (&dataquote.);
quit;

You either need to put the %unquote where I did or later on (such as in the %let or when it is used).

1
votes

The biggest problem with using this method based on TRANWRD() function is that you need to make sure your space delimited list has only one space between words. But since you are generating it with PROC SQL you should be fine. If it is entered manually then add a call to COMPBL() to clean up the source string.

You can create the string using macro quoting and then remove the macro quoting.

%let data =london paris;
%let qlist=%qsysfunc(tranwrd(&data,%str( ),%bquote(',')));
%let qlist=(%unquote(%bquote('&qlist')));

Or you could make the string using double quotes and then reverse the double quotes to single quotes.

%let data =london paris;
%let qlist=("%sysfunc(tranwrd(&data,%str( ),%bquote(",")))");
%let qlist=%sysfunc(translate(&qlist,'"',"'"));

Of course if you are not using the space delimited version for anything else then just generate the quoted version directly in PROC SQL. You can add the () later.

select quote(trim(city),"'") into :data separated by ',' ...
...
where city in (&data)
...