topic add single quotes and commas to a list of words in SAS Programming

add single quotes and commas to a list of words

ciro — Thu, 15 Feb 2018 11:33:18 GMT

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

Re: add single quotes and commas to a list of words

RW9 — Thu, 15 Feb 2018 11:39:06 GMT

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.

Re: add single quotes and commas to a list of words

Satish_Parida — Thu, 15 Feb 2018 11:58:00 GMT

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

Re: add single quotes and commas to a list of words

ballardw — Thu, 15 Feb 2018 15:55:02 GMT

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