Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
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, ds=bird, numval=1)
15 %mp_searchdata(lib=sashelp, ds=class, string=l,outobs=5)
16
17
18 Outputs zero or more tables to an MPSEARCH library with specific records.
19
20 @param [in] lib= The libref to search (should be already assigned)
21 @param [in] ds= The dataset to search (leave blank to search entire library)
22 @param [in] string= String value to search (case sensitive, can be partial)
23 @param [in] numval= Numeric value to search (must be exact)
24 @param [out] outloc= (0) Optionally specify the directory in which to
25 create the the output datasets with matching rows. By default it will
26 write them to a temporary subdirectory within the WORK folder.
27 @param [out] outlib= (MPSEARCH) Assign a different libref to the output
28 library containing the matching datasets / records
29 @param [in] outobs= set to a positive integer to restrict the number of
30 observations
31 @param [in] filter_text= (1=1) Add a (valid) filter clause to further filter
32 the results.
33
34 <h4> SAS Macros </h4>
35 @li mf_getuniquename.sas
36 @li mf_getvarlist.sas
37 @li mf_getvartype.sas
38 @li mf_mkdir.sas
39 @li mf_nobs.sas
40
41 @version 9.2
42 @author Allan Bowe
43**/
44
45%macro mp_searchdata(lib=
46 ,ds=
47 ,string= /* the query will use a contains (?) operator */
48 ,numval= /* numeric must match exactly */
49 ,outloc=0
50 ,outlib=MPSEARCH
51 ,outobs=-1
52 ,filter_text=%str(1=1)
53)/*/STORE SOURCE*/;
54
55%local table_list table table_num table colnum col start_tm check_tm vars type
56 coltype;
57%put process began at %sysfunc(datetime(),datetime19.);
58
59%if &syscc ge 4 %then %do;
60 %put %str(WAR)NING: SYSCC=&syscc on macro entry;
61 %return;
62%end;
63
64%if &string = %then %let type=N;
65%else %let type=C;
66
67%if "&outloc"="0" %then %do;
68 %let outloc=%sysfunc(pathname(work))/%mf_getuniquename();
69%end;
70
71%mf_mkdir(&outloc)
72libname &outlib "&outloc";
73
74/* get the list of tables in the library */
75proc sql noprint;
76select distinct memname into: table_list separated by ' '
77 from dictionary.tables
78 where upcase(libname)="%upcase(&lib)"
79%if &ds ne %then %do;
80 and upcase(memname)=%upcase("&ds")
81%end;
82 ;
83/* check that we have something to check */
84%if %length(&table_list)=0 %then %put library &lib contains no tables!;
85/* loop through each table */
86%else %do table_num=1 %to %sysfunc(countw(&table_list,%str( )));
87 %let table=%scan(&table_list,&table_num,%str( ));
88 %let vars=%mf_getvarlist(&lib..&table);
89 %if %length(&vars)=0 %then %do;
90 %put NO COLUMNS IN &lib..&table! This will be skipped.;
91 %end;
92 %else %do;
93 %let check_tm=%sysfunc(datetime());
94 /* prep input */
95 data &outlib..&table;
96 set &lib..&table;
97 where %unquote(&filter_text) and ( 0
98 /* loop through columns */
99 %do colnum=1 %to %sysfunc(countw(&vars,%str( )));
100 %let col=%scan(&vars,&colnum,%str( ));
101 %let coltype=%mf_getvartype(&lib..&table,&col);
102 %if &type=C and &coltype=C %then %do;
103 /* if a char column, see if it contains the string */
104 or ("&col"n ? "&string")
105 %end;
106 %else %if &type=N and &coltype=N %then %do;
107 /* if numeric match exactly */
108 or ("&col"n = &numval)
109 %end;
110 %end;
111 );
112 %if &outobs>-1 %then %do;
113 if _n_ > &outobs then stop;
114 %end;
115 run;
116 %put Search query for &table took
117 %sysevalf(%sysfunc(datetime())-&check_tm) seconds;
118 %if &syscc ne 0 %then %do;
119 %put %str(ERR)ROR: SYSCC=&syscc when processing &lib..&table;
120 %return;
121 %end;
122 %if %mf_nobs(&outlib..&table)=0 %then %do;
123 proc sql;
124 drop table &outlib..&table;
125 %end;
126 %end;
127%end;
128
129%put process finished at %sysfunc(datetime(),datetime19.);
130
131%mend mp_searchdata;