Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_searchdata.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Searches all data in a library
4  @details
5  Scans an entire library and creates a copy of any table
6  containing a specific string OR numeric value. Only
7  matching records are written out.
8  If both a string and numval are provided, the string
9  will take precedence.
10 
11  Usage:
12 
13  %mp_searchdata(lib=sashelp, string=Jan)
14  %mp_searchdata(lib=sashelp, numval=1)
15 
16 
17  Outputs zero or more tables to an MPSEARCH library with specific records.
18 
19  @param lib= the libref to search (should be already assigned)
20  @param ds= the dataset to search (leave blank to search entire library)
21  @param string= the string value to search
22  @param numval= the numeric value to search (must be exact)
23  @param outloc= the directory in which to create the output datasets with matching
24  rows. Will default to a subfolder in the WORK library.
25  @param outobs= set to a positive integer to restrict the number of observations
26  @param filter_text= add a (valid) filter clause to further filter the results
27 
28  <h4> Dependencies </h4>
29  @li mf_getvarlist.sas
30  @li mf_getvartype.sas
31  @li mf_mkdir.sas
32  @li mf_nobs.sas
33 
34  @version 9.2
35  @author Allan Bowe
36 **/
37 
38 %macro mp_searchdata(lib=sashelp
39  ,ds=
40  ,string= /* the query will use a contains (?) operator */
41  ,numval= /* numeric must match exactly */
42  ,outloc=%sysfunc(pathname(work))/mpsearch
43  ,outobs=-1
44  ,filter_text=%str(1=1)
45 )/*/STORE SOURCE*/;
46 
47 %local table_list table table_num table colnum col start_tm check_tm vars type coltype;
48 %put process began at %sysfunc(datetime(),datetime19.);
49 
50 %if &syscc ge 4 %then %do;
51  %put %str(WAR)NING: SYSCC=&syscc on macro entry;
52  %return;
53 %end;
54 
55 %if &string = %then %let type=N;
56 %else %let type=C;
57 
58 %mf_mkdir(&outloc)
59 libname mpsearch "&outloc";
60 
61 /* get the list of tables in the library */
62 proc sql noprint;
63 select distinct memname into: table_list separated by ' '
64  from dictionary.tables
65  where upcase(libname)="%upcase(&lib)"
66 %if &ds ne %then %do;
67  and upcase(memname)=%upcase("&ds")
68 %end;
69  ;
70 /* check that we have something to check */
71 proc sql
72 %if &outobs>-1 %then %do;
73  outobs=&outobs
74 %end;
75 ;
76 %if %length(&table_list)=0 %then %put library &lib contains no tables!;
77 /* loop through each table */
78 %else %do table_num=1 %to %sysfunc(countw(&table_list,%str( )));
79  %let table=%scan(&table_list,&table_num,%str( ));
80  %let vars=%mf_getvarlist(&lib..&table);
81  %if %length(&vars)=0 %then %do;
82  %put NO COLUMNS IN &lib..&table! This will be skipped.;
83  %end;
84  %else %do;
85  %let check_tm=%sysfunc(datetime());
86  /* build sql statement */
87  create table mpsearch.&table as select * from &lib..&table
88  where %unquote(&filter_text) and
89  (0
90  /* loop through columns */
91  %do colnum=1 %to %sysfunc(countw(&vars,%str( )));
92  %let col=%scan(&vars,&colnum,%str( ));
93  %let coltype=%mf_getvartype(&lib..&table,&col);
94  %if &type=C and &coltype=C %then %do;
95  /* if a char column, see if it contains the string */
96  or ("&col"n ? "&string")
97  %end;
98  %else %if &type=N and &coltype=N %then %do;
99  /* if numeric match exactly */
100  or ("&col"n = &numval)
101  %end;
102  %end;
103  );
104  %put Search query for &table took %sysevalf(%sysfunc(datetime())-&check_tm) seconds;
105  %if &sqlrc ne 0 %then %do;
106  %put %str(WAR)NING: SQLRC=&sqlrc when processing &table;
107  %return;
108  %end;
109  %if %mf_nobs(mpsearch.&table)=0 %then %do;
110  drop table mpsearch.&table;
111  %end;
112  %end;
113 %end;
114 
115 %put process finished at %sysfunc(datetime(),datetime19.);
116 
117 %mend;