How do you in SAS return the list of all columns in all tables in a library that contain a target value?

4
votes

I am trying to map out fields I see in an application to columns in the source database using SAS EG.

If I search for 'SomeString' or someNumericValue in Library = SomeLibrary I want the code to output a table that lists the tableName ColumnName that contains the value searched.

Proc SQL: Select * columns C from all tables in Library L that contain the value or string = 'SomeValue'

3
searchsasenterprise-guide

3 Answers

0
votes

It is a nice question, i myself wanted to develop the code for me .. you can try following code to find the table names from a library, exact variable names which has the required value

Modified Code

libname temp "Y:\temp\t";
data temp.aa;
a=0;
b=0;
test="String";
run;

data temp.bb;
a=1;
c=0;
d=1;
run;

data temp.cc;
a=0;
b=1;
e=1;
run;

proc sql;
create table info
as
select memname as table, name as column from dictionary.columns
where upcase(type)="NUM" /*upcase(type)="CHAR"*/
and libname='TEMP'
order by memname;
quit;

options merror mprint nosymbolgen nomlogic;
data info1;
length coltab $1000.;
 set info;
 newtab=catx("_","TEMPT",_n_);
 condition=column||"=1"; /*Set Desired value here*/
 tab=("'"||table||"' as tab_name");
 var=("'"||column||"' as var_name");
 coltab="create table "||newtab||" as Select "||column||","||tab||","||var||" from temp."||table|| "where "||condition||";";
run;

proc sql noprint;
select count(*) into: nobs from info1;
quit;

%macro process;
%do i=1 %to &nobs;
    Data _null_;
        Set info1(firstobs=&i obs=&i);
        call symput('query',coltab);
    run;
    proc sql noprint;
        &Query;
    quit;
%end;
%mend;

%process;

proc sql noprint;
select distinct memname into :gt separated by " " from dictionary.columns where memname like '%TEMPT%';
quit;

%macro split(var);
%let var_c=%sysfunc(countw(&var));
%do i=1 %to &var_c;
    %let var_t=%sysfunc(scan(&var,&i));

    proc sql noprint;
    select count(*) into :cnt from &var_t;
    quit;

    %if &cnt=0 %then
    %do;
        proc datasets lib=work nolist;   
        delete &var_t; 
        quit;
        run;
    %end;
%end;
%mend split;

%split(&gt);

proc sql noprint;
select distinct memname into :gt0 separated by " " from dictionary.columns where memname like '%TEMPT%';
quit;

data all;
 set &gt0;
 keep tab_name var_name;
run;

proc sort data=all; by tab_name; run;

data final;
length vars $100.;
 set all;
  by tab_name;
  retain vars '';
  if first.tab_name then vars=var_name;
  else vars=catx(",",vars,var_name);
  if last.tab_name;
  drop var_name;
run;

proc print data=final; run;
0
votes

Proc contents can create a table of data set names to scan. A scanning macro, say %scanner, can be written and invoked for each data set via call execute. The results of the scan, the data set name and column containing the target, can be appended to an 'all results' table.

Example:

For simplicity it is presumed no data set has more than 10K variables of the target value type -- the code issues a warning if the scanning will be clipped.

Note: Example of string target would be ..., target="Jane", ...

%macro scanner (libname=, memname=, target=20500, flagMax = 10000);
  %local type;

  %if %qsysfunc(dequote(&target)) = %superq(target) %then 
    %let type = _numeric_;
  %else 
    %let type = _character_;

  data hits(keep=__libname __memname __varname);
    array __flag (&flagMax) _temporary_;

    set &libname..&memname;

    array __candidates &type;

    if dim(__candidates) = 0 then stop;

    do __index = 1 to min (dim(__candidates), &flagMax);
      if not __flag(__index) then 
        if __candidates(__index) = &target then do;
          length __libname $8;
          length __memname __varname $32;
          __libname = "&libname";
          __memname = "&memname";
          __varname = vname(__candidates(__index));
          __flag(__index) = 1;

          OUTPUT;
        end;          
    end;

    if _n_ = 1 then
      if dim(__candidates) > &flagMax then put "WARNING: &memname has more than &flagMax variables - scanning will be clipped. Increase flagMax=.";
  run;

  proc append base=hasTarget data=hits(rename=(__libname=libname __memname=memname __varname=varname));
  run;
%mend;

proc sql;
  create table hasTarget (libname char(8), memname char(32), varname char(32));
quit;

%let libname = SASHELP;

ods noresults;
ods output members=datasets;
proc datasets library=&libname memtype=data;
run;
quit;
ods results;

data _null_;
  set datasets(keep=name memtype);
  where memtype = 'DATA';

  call execute (cats('%nrstr(%scanner(libname=' || "&LIBNAME., " || "memname=", name, '))'));
run;
0
votes

Great challenge! I can get you some of the way there - the mp_searchdata macro of the SASjs macro core library will query all tables in a library (source database) for a string or numeric value. It returns all columns, but will filter for only matching records.

To execute:

/* import library */
filename mc url "https://raw.githubusercontent.com/sasjs/core/main/all.sas";
%inc mc;

/* run macro */
%mp_searchdata(lib=yourlib, string=SomeString)