topic Re: change a space delimited list into a comma delimited list with each element enclosed by quotatio in SAS Programming

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

gleebglorb — Sat, 03 Apr 2021 15:35:20 GMT

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"

Re: change a space delimited list into a comma delimited list with each element enclosed by quotatio

sbxkoenk — Sat, 03 Apr 2021 15:46:21 GMT

Change your select statement to:

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

Koen

Re: change a space delimited list into a comma delimited list with each element enclosed by quotatio

Tom — Sat, 03 Apr 2021 15:58:35 GMT

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);

Re: change a space delimited list into a comma delimited list with each element enclosed by quotatio

Ksharp — Sun, 04 Apr 2021 12:17:16 GMT

%macro test(name=);

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

%mend;

%test(name = Janet Mary John)