topic Re: Searching multiple columns and grouping results. in SAS Programming

Searching multiple columns and grouping results.

jrevinzon — Mon, 23 Aug 2021 19:10:39 GMT

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.

Re: Searching multiple columns and grouping results.

novinosrin — Mon, 23 Aug 2021 19:13:33 GMT

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!

Re: Searching multiple columns and grouping results.

Reeza — Mon, 23 Aug 2021 19:18:49 GMT

Use WHICHC() to see fi the term is present
If present, loop through and concatenate the names of the variables.

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.