@mikepeters
I've missed that you wrote "I am a novice SAS user" so the sample code I've pointed you to is likely still a bit too advanced for you to use as a starting point.
What you basically need to do: Starting from a top folder create a list of all the directories (recursive). That's actually quite simple to achieve with a Unix/Linux LS command or a Windows DIR command. Challenge is: A lot of sites don't allow SAS EG users to issue such OS commands out of SAS (Option NOXCMD set. You can run Proc OPTIONS; Run: to check if the option is XCMD or NOXCMD).
The SAS sample code I've pointed you to provides a SAS way for a directory listing which also works when NOXCMD is set.
Once you've got a directory listing you then issue as SAS libname statement per directory and then query the SAS dictionary tables for the just defined libref to the all the entries of column names you're searching for.
Writing such SAS code is in my opinion not beginner level. Below working code based on the sample code I've referenced earlier.
Just copy paste the code into your environment. The only thing you need to change is the following line of code:
%memlist(<path to top folder>, outtbl=memlist, varlist=<one or more variable names to search for>)
First parameter needs to be the fully qualified path to top folder you want to search from.
I won't explain the details how below code works also because a beginner should first upskill in Base SAS language before diving into SAS Macro language.
%macro memlist(dir, first=Y, outtbl=_memlist, varlist=);
%local filrf rc did memcnt name i;
%let varlist=%unquote(&varlist);
%if &first=Y %then
%do;
data &outtbl;
stop;
length path $500;
set sashelp.vcolumn(keep=memname name rename=(memname=table name=variable_name));
run;
data _null_;
length varlist $32767;
varlist=compbl("&varlist");
varlist=cats("'",tranwrd(strip(varlist),' ',"','"),"'");
call symputx('varlist',upcase(strip(varlist)));
stop;
run;
%end;
/* Assigns a fileref to the directory and opens the directory */
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));
/* Make sure directory can be opened */
%if &did eq 0 %then %do;
%put Directory &dir cannot be open or does not exist;
%return;
%end;
libname temp1234 "&dir" access=readonly;
proc sql;
create table _v_&outtbl as
select
"&dir" as Path length=500,
memname as table,
name as variable_name
from dictionary.columns
where
libname='TEMP1234'
and upcase(name) in (&varlist)
;
quit;
libname temp1234 clear;
proc append base=&outtbl data=_v_&outtbl;
run;quit;
proc datasets lib=work nolist nowarn;
delete _v_&outtbl;
run;quit;
/* Loops through entire directory */
%do i = 1 %to %sysfunc(dnum(&did));
/* Retrieve name of each file */
%let name=%qsysfunc(dread(&did,&i));
/* If directory name call macro again */
%if %qscan(&name,2,.) = %then %do;
data _null_;
if findc("&dir",'/')>0 then call symputx('slash','/','l');
else call symputx('slash','\','l');
stop;
run;
%memlist(&dir.&slash.%unquote(&name), first=N, outtbl=&outtbl, varlist=%nrbquote(&varlist))
%end;
%end;
/* Closes the directory and clear the fileref */
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));
%mend memlist;
/* First parameter is the directory of where your table are stored. */
/* Parameter "varlist" allows to pass in a blank separated list of variable names to search for */
/* Parameter "outtbl" allows to define the name of the output table with the search result */
%memlist(<path to top folder>, outtbl=memlist, varlist=<one or more variable names to search for>)
proc print data=memlist;
run;
... View more