Searching multiple columns and grouping results.

[jrevinzon]

Lets say I have 100 columns with various character strings. I want to search all of the columns to see if they contain the word "Apple". I then want to create another column that creates a list of all the columns that contained the word "Apple". 

Example:

Col1     Col2     Col3       Results 

Apple    Berry   Pear       Col1 

Berry     Apple   Apple     Col1, Col 2

Pear     Pear     Pear       None 

Any help with this would be appreciated. 

[novinosrin]

Hi @jrevinzon  A quick clue for you. Look at WHICHC/WHICHN group of functions. I am lazy to write the code. But there are gazillion examples online. All the best!

[Reeza]

1. Use WHICHC() to see fi the term is present

2. If present, loop through and concatenate the names of the variables. 

3. data want;
   set have;
   
   array _cols (*) col1-col3;
   
   length results $300.;
   call missing(Results);
   
   if whichc("Apple", of _cols(*)) then do i=1 to dim(_cols);
       if _cols(i) = "Apple" then results = catx(", ", results, vname(_cols(i)));
   end;
   
   run;

---

@jrevinzon wrote:

Lets say I have 100 columns with various character strings. I want to search all of the columns to see if they contain the word "Apple". I then want to create another column that creates a list of all the columns that contained the word "Apple". 

Example:

Col1     Col2     Col3       Results 

Apple    Berry   Pear       Col1 

Berry     Apple   Apple     Col1, Col 2

Pear     Pear     Pear       None 

Any help with this would be appreciated. 

---