CORE
mp_searchdata.sas
Go to the documentation of this file.
1 
20 %macro mp_searchdata(lib=sashelp
21  ,ds= /* this macro will be upgraded to work for single datasets also */
22  ,type=C /* this macro will be updated to work for numeric data also */
23  ,string=Jan
24  ,outloc=%sysfunc(pathname(work))/mpsearch
25 );
26 
27 %local table_list table table_num table colnum col start_tm vars;
28 %put process began at %sysfunc(datetime(),datetime19.);
29 
30 %mf_mkdir(&outloc)
31 libname mpsearch "&outloc";
32 
33 /* get the list of tables in the library */
34 proc sql noprint;
35 select distinct memname into: table_list separated by ' '
36  from dictionary.tables where upcase(libname)="%upcase(&lib)";
37 /* check that we have something to check */
38 proc sql;
39 %if %length(&table_list)=0 %then %put library &lib contains no tables!;
40 /* loop through each table */
41 %else %do table_num=1 %to %sysfunc(countw(&table_list,%str( )));
42  %let table=%scan(&table_list,&table_num,%str( ));
43  %let vars=%mf_getvarlist(&lib..&table);
44  %if %length(&vars)=0 %then %do;
45  %put NO COLUMNS IN &lib..&table! This will be skipped.;
46  %end;
47  %else %do;
48  /* build sql statement */
49  create table mpsearch.&table as select * from &lib..&table
50  where 0
51  /* loop through columns */
52  %do colnum=1 %to %sysfunc(countw(&vars,%str( )));
53  %let col=%scan(&vars,&colnum,%str( ));
54  %put &col;
55  %if %mf_getvartype(&lib..&table,&col)=C %then %do;
56  /* if a char column, see if it contains the string */
57  or (&col ? "&string")
58  %end;
59  %end;
60  ;
61  %if %mf_nobs(mpsearch.&table)=0 %then %do;
62  drop table mpsearch.&table;
63  %end;
64  %end;
65 %end;
66 
67 %put process finished at %sysfunc(datetime(),datetime19.);
68 
69 %mend;