SAS Studio - How to utilise nested WHERE conditions for string searches

Question

As part of a bigger project, I am trying to search through multiple tables for common columns. However I dont know the number of tables, number of columns or the exact names of columns so I cannot hard code the counts or names. My main issue is I am trying to search for a keyword from a list, in a long column name, such as Your_Email_Address using "Email". At the moment I can only get the column search working by listing each "NAME" search function individually (which isnt viable for this). I have tried numerous variations of where NAME LIKE/contains(IN(&OPTIONS.)) but to no success. Does anyone know how to properly combine the WHERE functions so it will successfully search and select the column names for the containing keyword from the &OPTIONS list? My code snippet so far is:

options mlogic mprint symbolgen;
%*LOOP;
%macro JOINWRAP;

%let OPTIONS = USERNAME
EMAIL
AGE
ETC...
;
%let dataset1 = A7;
%let dataset2 = Subset;
%let OPTIONS_count = %sysfunc(countw(&OPTIONS.));

%*read columns into macro variables;
%do j = 1 %to 2;
proc sql ;
    select NAME into :CLMNS&j SEPARATED BY ", " /*reading columns from each proc contents 
    into a seperate macro variable, so clmns1 = columns from A7*/
    from work.COLS&j /*cols1 = A7 PROC CONTENTS previously defined */
    where NAME CONTAINS "%scan(&OPTIONS.,1)" or NAME contains "%scan(&OPTIONS.,2)"
    ;
    quit;
%put &&CLMNS&j;
%end;
...
run;
/* %end; */
%mend JOINWRAP;
%JOINWRAP;

Answer 1

I assume you are trying to do something like:

data want;
  set contents;
  where upcase(name) contains 'EMAIL'
    or  upcase(name) contains 'USERNAME'
  ;
run;

If the list of substrings to search for is constant just hard code it.

If it varies then it will be much easier to do it with a DATA step than trying to force PROC SQL and/or macro language to do the work.

So something like this:

%let wordlist=EMAIL USERNAME;
data want;
  set contents;
  found=0;
  do index=1 to countw("&wordlist") until (found);
    if index(upcase(name),scan("&wordlist",index)) then found=1;
  end;
  if found;
run;