DATA Step, Macro, Functions and more

add single quotes and commas to a list of words

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

add single quotes and commas to a list of words

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

 


Accepted Solutions
Solution
‎02-15-2018 07:19 AM
Frequent Contributor
Posts: 109

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

[ Edited ]

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

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,376

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

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.

Solution
‎02-15-2018 07:19 AM
Frequent Contributor
Posts: 109

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

[ Edited ]

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

 

Super User
Posts: 13,283

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 138 views
  • 3 likes
  • 4 in conversation