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
24 matching rows. Will default to a subfolder in the WORK library.
25 @param outobs= set to a positive integer to restrict the number of
26 observations
27 @param filter_text= add a (valid) filter clause to further filter the results
28
29 <h4> SAS Macros </h4>
30 @li mf_getvarlist.sas
31 @li mf_getvartype.sas
32 @li mf_mkdir.sas
33 @li mf_nobs.sas
34
35 @version 9.2
36 @author Allan Bowe
37**/
38
39%macro mp_searchdata(lib=sashelp
40 ,ds=
41 ,string= /* the query will use a contains (?) operator */
42 ,numval= /* numeric must match exactly */
43 ,outloc=%sysfunc(pathname(work))/mpsearch
44 ,outobs=-1
45 ,filter_text=%str(1=1)
46)/*/STORE SOURCE*/;
47
48%local table_list table table_num table colnum col start_tm check_tm vars type
49 coltype;
50%put process began at %sysfunc(datetime(),datetime19.);
51
52%if &syscc ge 4 %then %do;
53 %put %str(WAR)NING: SYSCC=&syscc on macro entry;
54 %return;
55%end;
56
57%if &string = %then %let type=N;
58%else %let type=C;
59
60%mf_mkdir(&outloc)
61libname mpsearch "&outloc";
62
63/* get the list of tables in the library */
64proc sql noprint;
65select distinct memname into: table_list separated by ' '
66 from dictionary.tables
67 where upcase(libname)="%upcase(&lib)"
68%if &ds ne %then %do;
69 and upcase(memname)=%upcase("&ds")
70%end;
71 ;
72/* check that we have something to check */
73proc sql
74%if &outobs>-1 %then %do;
75 outobs=&outobs
76%end;
77;
78%if %length(&table_list)=0 %then %put library &lib contains no tables!;
79/* loop through each table */
80%else %do table_num=1 %to %sysfunc(countw(&table_list,%str( )));
81 %let table=%scan(&table_list,&table_num,%str( ));
82 %let vars=%mf_getvarlist(&lib..&table);
83 %if %length(&vars)=0 %then %do;
84 %put NO COLUMNS IN &lib..&table! This will be skipped.;
85 %end;
86 %else %do;
87 %let check_tm=%sysfunc(datetime());
88 /* build sql statement */
89 create table mpsearch.&table as select * from &lib..&table
90 where %unquote(&filter_text) and
91 (0
92 /* loop through columns */
93 %do colnum=1 %to %sysfunc(countw(&vars,%str( )));
94 %let col=%scan(&vars,&colnum,%str( ));
95 %let coltype=%mf_getvartype(&lib..&table,&col);
96 %if &type=C and &coltype=C %then %do;
97 /* if a char column, see if it contains the string */
98 or ("&col"n ? "&string")
99 %end;
100 %else %if &type=N and &coltype=N %then %do;
101 /* if numeric match exactly */
102 or ("&col"n = &numval)
103 %end;
104 %end;
105 );
106 %put Search query for &table took
107 %sysevalf(%sysfunc(datetime())-&check_tm) seconds;
108 %if &sqlrc ne 0 %then %do;
109 %put %str(WAR)NING: SQLRC=&sqlrc when processing &table;
110 %return;
111 %end;
112 %if %mf_nobs(mpsearch.&table)=0 %then %do;
113 drop table mpsearch.&table;
114 %end;
115 %end;
116%end;
117
118%put process finished at %sysfunc(datetime(),datetime19.);
119
120%mend mp_searchdata;