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=(SASHELP) Space separated list of libraries to search for columns
13 @param cols= Space separated list of column names to search for (not case
14 sensitive)
15 @param outds=(mp_searchcols) the table to create with the results. Will have
16 one line per table match.
17 @param match=(ANY) The match type. Valid values:
18 @li ANY - The table contains at least one of the columns
19 @li WILD - The table contains a column with a name that partially matches
20
21 @version 9.2
22 @author Allan Bowe
23**/
24
25%macro mp_searchcols(libs=sashelp
26 ,cols=
27 ,outds=mp_searchcols
28 ,match=ANY
29)/*/STORE SOURCE*/;
30
31%put &sysmacroname process began at %sysfunc(datetime(),datetime19.);
32
33/* get the list of tables in the library */
34proc sql;
35create table _data_ as
36 select distinct upcase(libname) as libname
37 , upcase(memname) as memname
38 , upcase(name) as name
39 from dictionary.columns
40%if %sysevalf(%superq(libs)=,boolean)=0 %then %do;
41 where upcase(libname) in ("IMPOSSIBLE",
42 %local x;
43 %do x=1 %to %sysfunc(countw(&libs));
44 "%upcase(%scan(&libs,&x))"
45 %end;
46 )
47%end;
48 order by 1,2,3;
49
50%local tempds;
51%let tempds=&syslast;
52data &outds;
53 set &tempds;
54 length cols matchcols $32767;
55 cols=upcase(symget('cols'));
56 colcount=countw(cols);
57 by libname memname name;
58 if _n_=1 then do;
59 putlog "Searching libs: &libs";
60 putlog "Searching cols: " cols;
61 end;
62 if first.memname then do;
63 sumcols=0;
64 retain matchcols;
65 matchcols='';
66 end;
67%if &match=ANY %then %do;
68 if findw(cols,name,,'spit') then do;
69 sumcols+1;
70 matchcols=cats(matchcols)!!' '!!cats(name);
71 end;
72%end;
73%else %if &match=WILD %then %do;
74 if _n_=1 then do;
75 retain wcount;
76 wcount=countw(cols);
77 drop wcount;
78 end;
79 do i=1 to wcount;
80 length curword $32;
81 curword=scan(cols,i,' ');
82 drop curword;
83 if index(name,cats(curword)) then do;
84 sumcols+1;
85 matchcols=cats(matchcols)!!' '!!cats(curword);
86 end;
87 end;
88%end;
89
90 if last.memname then do;
91 if sumcols>0 then output;
92 if sumcols=colcount then putlog "Full Match: " libname memname;
93 end;
94 keep libname memname sumcols matchcols;
95run;
96
97proc sort; by descending sumcols memname libname; run;
98
99proc sql;
100drop table &tempds;
101%put &sysmacroname process finished at %sysfunc(datetime(),datetime19.);
102
103%mend mp_searchcols;