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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.