4

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'

Richard
  • 25,390
  • 3
  • 25
  • 38
SQALEX101
  • 209
  • 1
  • 3
  • 16

3 Answers3

0

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)
Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
  • I get the following Errors when trying to execute this: ERROR: The connection was reset by a peer.. ERROR: Cannot open %INCLUDE file MC. Even when I copy the code in, I get an error when trying to execute the macro: ERROR 180-322: Statement is not valid or it is used out of proper order. – SQALEX101 May 31 '19 at 13:45
  • you may not have access from your web server. Just download and compile the macros separately: https://raw.githubusercontent.com/sasjs/core/main/all.sas – Allan Bowe Jul 28 '20 at 23:40
0

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;
Community
  • 1
  • 1
Rhythm
  • 680
  • 5
  • 9
  • I get the following error after switching my search string and Variable. My string is only 10 characters long... ERROR: The length of the value of the macro variable COLTAB (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters. – SQALEX101 May 31 '19 at 13:44
  • Not too sure if this code handles tables/datasets that have a mix of character and numeric variables. Is there a way for it to ignore numeric columns when iterating? – SQALEX101 May 31 '19 at 14:16
  • I have modified the code for your first error, it should be fine now. Because now it will loop through each observation and execute the query separately, instead of creating a big string. – Rhythm May 31 '19 at 17:14
  • Also, to answer your second concern - yes you can control the types char/num based on the logic - upcase(type)="CHAR" .. or "NUM" while extracting data from dictionary.columns. So I have tweaked the sample dataset a bit and included a character variablefor your reference. I am using only Numbers and ignoring characters, you can do vice-versa.. so if you would want to search a string then change this line : condition=column||"='String'"; /*Set Desired value here*/ and use upcase(type)="CHAR" – Rhythm May 31 '19 at 17:16
0

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;
Richard
  • 25,390
  • 3
  • 25
  • 38
  • Also, how would one skip specific tables in the searched library? for instance in has tables A,B,C,D and I know Table C is large and irrelevant, then can I tell the macro to skip Table C in the automation? – SQALEX101 Aug 01 '19 at 13:32
  • Add a `where` or `if` to the final `data _null_` before the `execute` that launches the scans. For example: `if NOT (libname="BIGGY" and memname="C");` – Richard Aug 01 '19 at 14:17
  • Amazing solution! – SQALEX101 Aug 01 '19 at 14:19
  • Where do I specify the lib to search: in `%let libname = SASHELP;` change `SASHELP` to my lib? Then confirming the string portion, change the string value in `target=20500` to something like `target="Jane"`? – md2614 Mar 02 '20 at 19:33
  • You can specify those directly in the `call execute`. `call execute (cats('%nrstr(%scanner(target="Jane", libname=MyLib, memname=', name, '))'));` – Richard Mar 02 '20 at 19:48