change a space delimited list into a comma delimited list with each element enclosed by quotation

[gleebglorb]

I am writing a macro that takes in a list and selects records from a dataset with those names.  

It will look something like this (it has to be done in a macro with a proc sql so I can't do this in a data step):

%macro test (name = John Jake Mary);

proc sql; 

select * from names where name in (%upcase(%sysfunc(tranwrd("&name",%str( ),%str(,))))); 

quit;

%mend;

Right now, all it is doing is just outputting this "John, Jake, Mary" but I want there to be quotations around each element in the list.  so the output looks like this "John", "Jake", "Mary"

[sbxkoenk]

Change your select statement to:

select * from names where name in (%upcase(%sysfunc(tranwrd("&name",%str( ),%str(",")))));

Koen

[Tom · Accepted solution]

Because you didn't ask it to insert the quotes into the middle.  Don't just change the spaces to commas. Change them to commas with quotes around them.

where name in (%sysfunc(tranwrd(%upcase("&name"),%str( ),",")))

You might also want to use single quotes instead, especially is you might be pushing this code into some remote database where only single quotes are used for string literals.

Also watch out for extra spaces, you might want to use COMPBL() to collapse them to single spaces.

%macro test(name);
%let name=%sysfunc(compbl(&name));
proc sql; 
select * from names 
  where name in (%sysfunc(tranwrd(%upcase(%str(%')&name%str(%')),%str( ),',')))
;
quit;
%mend;

%test(name = John Jake Mary);

[Ksharp]

%macro test(name=);

proc sql; 
select * from sashelp.class where findw(symget('name'),strip(name),' ','i'); 
quit;

%mend;

%test(name = Janet Mary John)