Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_searchcols.sas
Go to the documentation of this file.
1 /**
2  @file mp_searchcols.sas
3  @brief Searches all columns in a library
4  @details
5  Scans a set of libraries and creates a dataset containing all source tables
6  containing one or more of a particular set of columns
7 
8  Usage:
9 
10  %mp_searchcols(libs=sashelp work, cols=name sex age)
11 
12  @param libs=
13  @version 9.2
14  @author Allan Bowe
15 **/
16 
17 %macro mp_searchcols(libs=sashelp
18  ,cols=
19  ,outds=mp_searchcols
20 )/*/STORE SOURCE*/;
21 
22 %put &sysmacroname process began at %sysfunc(datetime(),datetime19.);
23 
24 /* get the list of tables in the library */
25 proc sql;
26 create table _data_ as
27  select distinct upcase(libname) as libname
28  , upcase(memname) as memname
29  , upcase(name) as name
30  from dictionary.columns
31 %if %sysevalf(%superq(libs)=,boolean)=0 %then %do;
32  where upcase(libname) in ("IMPOSSIBLE",
33  %local x;
34  %do x=1 %to %sysfunc(countw(&libs));
35  "%upcase(%scan(&libs,&x))"
36  %end;
37  )
38 %end;
39  order by 1,2,3;
40 
41 data &outds;
42  set &syslast;
43  length cols matchcols $32767;
44  cols=upcase(symget('cols'));
45  colcount=countw(cols);
46  by libname memname name;
47  if _n_=1 then do;
48  putlog "Searching libs: &libs";
49  putlog "Searching cols: " cols;
50  end;
51  if first.memname then do;
52  sumcols=0;
53  retain matchcols;
54  matchcols='';
55  end;
56  if findw(cols,name,,'spit') then do;
57  sumcols+1;
58  matchcols=cats(matchcols)!!' '!!cats(name);
59  end;
60  if last.memname then do;
61  if sumcols>0 then output;
62  if sumcols=colcount then putlog "Full Match: " libname memname;
63  end;
64  keep libname memname sumcols matchcols;
65 run;
66 
67 proc sort; by descending sumcols memname libname; run;
68 
69 %put &sysmacroname process finished at %sysfunc(datetime(),datetime19.);
70 
71 %mend;