Searching for a single observation among multiple tables

[EricM0628]

Is it possible to search multiple tables within a database for a single observation value (using Enterprise Guide 5.1)?  I have a newly connected database that I'm trying to map, and I'm having difficulty finding a specific variable.  I have a report from the front end that provides some data, and I'd like to search all of the tables within the database for a single value.  In order to get the report schema I'll have to go to the vendor, which has a cost associated to it.  So, I'm attempting to avoid going down that road.  I appreciate any suggestions.

[ballardw]

Assuming you know which library you can run the following code to table names (I don't know the tasks for EG and don't have 5.1)

proc sql

     select memname

     from dictionary.columns

     where libname='LIB' and upcase(name)="YOURVARIABLENAMEINCAPS";

quit;

Replace LIB with your library name. If you don't know the library then leave out the Libname part and add " , Libname" to the select clause.

[EricM0628]

Thanks for the quick reply, but I'm actually looking for an observation value.  I've been able to pull all of the variable names, but that isn't helping me find the correct one.   I need to find the table and variable that stores a particular observation.

[ballardw]

You title says one thing then the text of your post included

and I'm having difficulty finding a specific variable

so I started with the easy part.

Is this value in a known variable or do you have to search all variables in all data sets? Do you know which library it should be in?

Is it character or numeric? If it is a date do you know if dates were imported as SAS date valued variables or character?

What if the value appears more than once? Do you want a list of all variables and datasets?

If the value is character are you looking for an exact match, i.e. case, leading blanks, or can it be part just part of text variable?

[EricM0628]

Sorry for the confusion.

I need to search all variables within all data sets, but I do know the library it's in.  The observation will be character, and not a date.  If it appears more than once, I'd like to see all variables and datasets that it appears in.  It's ok if it's just part of a text variable.

[ballardw · Accepted solution]

This seems to work for me:

%let searchval = 1003349;      /* AN EXAMPLE VALUE, DO NOT USE QUOTES FOR YOURS*/

/* the macro variable is so that I use the same in two places*/

/* this gets the names of the data sets in the specified library (use UPPER case for the name) with

at least one character variable long enough to hold the string specified in SEARCHVAL*/

proc sql;
   create table work.datasets as
   select distinct memname
   from ( select * from dictionary.columns
          where libname='LIBRARY' and type='char'
          and length("&searchval") le length)
   ;
quit;

/* this is a brute force search of a data set, the default is to seach the library you specify above if you

replace the value in the macro definition. Hardcoding the name of the output file reference isn't best practice,

wouldn't be hard to add another parameter*/

%macro findit (lib=LIBRARY,dataset=,value=);
data _null_;
   set &lib..&dataset;
   file tables mod ;
   array c _character_;
   length name $ 32 ;
   do i = 1 to dim(c);
      name = vname(c);
      if index( c,strip("&value")) > 0 then         
         put "Value: &value" +4 "Dataset: &dataset" +4 "Record:" _n_ +4"Variable: " name;
   end;
run;
%mend;

/* specify the name of the output file path and name in a filename statement*/

filename tables "path\tables.txt"  mod;

/* loop through the datasets found above and call the search macro for each*/
data _null_;
   set work.datasets;
   call execute('%findit(dataset='||memname||',value='||"&searchval"||')');
run;

[EricM0628]

Thanks, it looks like this worked.  I appreciate the assistance.

[Astounding]

Just a suggestion ...

Don't print out all occurrences found.  If the user enters the wrong string, you can end up trying to print millions of records.  Just put them into a data set and print later if warranted.