Hi,
In the first record of a dataset there is a variable with several words separated by space. eg. like this: John James Will Bob
I don't know the number of words in the variable.
I would like to load in a macro variable the string: 'John','James','Will','Bob' .
the macro variable is to be used in a following step in a where statement like this: where name not in (&listnames) .
Any help is appreciated.
Thanks very much in advance
this works (Assuming there is no gap in between a single name like de Morgan)
data have;
infile datalines dsd;
input names:$200.;
datalines;
John James Will Bob PK SK DK Simon
;
run;
data _null_;
set have;
if _n_=1 then do;
names1="'"||tranwrd(trim(names), " ", "','")||"'";
call symput('names',names1);
end;
run;
%put &names.;
Just don't. A far simpler more robust method is to use Base SAS:
/* This creates a dataset with all the distinct values from the one string */ data list (keep=word); set your_data; length word $200; do i=1 to countw(str,' '); word=scan(str,i,' '); output; end; run; proc sort data=list nodupkey; by word; run; /* Now we use the list in a where clause */ proc sql; create table want as select * from have where name not in (select word from list); quit;
This will expand and decrease as needed for the data items, and requires far less messy code.
this works (Assuming there is no gap in between a single name like de Morgan)
data have;
infile datalines dsd;
input names:$200.;
datalines;
John James Will Bob PK SK DK Simon
;
run;
data _null_;
set have;
if _n_=1 then do;
names1="'"||tranwrd(trim(names), " ", "','")||"'";
call symput('names',names1);
end;
run;
%put &names.;
Note that creating a macro variable of this type means that unless you take extra steps you cannot pass it as a parameter value in a macro, use certain functions such as %scan or %substr with it and get expected results.
Note that for your stated use the COMMA is not needed and is what complicates passing as a parameter or use with %scan and %substr
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.