add single quotes and commas to a list of words

[ciro]

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

[RW9]

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.

[Satish_Parida · Accepted solution]

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

[ballardw]

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