Macro to automate searching different datasets in different folders for a specific column name

[BrinaLi]

Hello!

I would like to create a macro that will automate searching through several folders. I will have multiple reports, therefore multiple folders with datasets. I would like to create a macro where it searches each folder(each folder represents metrics for a specific report), and in each dataset, look to see if it contains a specified column. If at least 1 of the datasets includes the column, then I want my macro to include my row-level-security column (created in a previous step) in all the datasets for that report. If at least 1 does not, then do not insert the column in any dataset for that report

So far, for testing, this is what I have. I am still learning and I apologize if my code or what I am trying to accomplish isn’t clear.

Thank you!

Data contents_all; format empname $32.; stop; run;

Proc contents noprint data= MetricFolder1. Dataset1 memtype=view out=contents (keep=empname); run;

Proc append base=contents_all data=contents; run;

Proc contents noprint data= MetricFolder1. Dataset2 memtype=view out=contents (keep=empname); run;

Proc append base=contents_all data=contents; run;

%global test;

Data _null_;

Set contents_all;

If  rls_name=’empname’ then do;

              Call (symput(‘test’,’Y’);

              Stop;

End;

Run;

Each report lives under a main report folder, but the number of datasets under each subfolder could be different

Metrics Folder <-main folder for reports

• MetricFolder1 <- Data for Report A
  • Dataset1
  • Dataset2
  • Dataset3
• MetricFolder2 <- Data for Report B
  • Dataset1
  • Dataset2
  • Dataset3
  • Dataset4
• MetricFolder3 <- Data for Report C
  • Dataset1
  • Dataset2

[JosvanderVelden]

Have you analyzed the option of using dictionary tables/views? See https://documentation.sas.com/doc/en/lrcon/9.4/p13650yz8q04csn1m2xojl26ljsc.htm & https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/070-30.pdf

[ballardw]

Libraries, not folders. If you think "assign a library" to each folder, or if the data set names are not duplicated in any of the folders, then this is trivial to select the data sets containing any given variable (or list of variables). If you have lots of sets it may take a bit of time.

After the libraries are assigned:

proc sql;
   create table thisvar as
   select libname, memname, name
   from dictionary.columns 
   where upcase(name)='NAMEOFVARYOUWANT'
   /* or if there are multiple variables */
   /* where upcase(name) in ('THISVAR' 'THATVAR' 'OTHERVAR') */
   ;
run;

The set Work.THISVAR will have the names of all libraries and data sets with the variable name(s) desired.

The dictionary.columns does not store the names of variables in fixed case so I use the UPCASE function to get the variable regardless of which case the name was created with.

Your bit about "row level security" column bit needs more explanation. It sounds like you expect to ADD a variable (and what values would that be) and you have provided zero information about where/how any of that is kept for use.