Identify given variable from multiple libraries & tables

[brulard]

hi,

If someone could suggest an approach to solve the following:

-From multiple libraries;

-scan each table from each library; 

- Flag if find variable 'ID_Number';

-Return location of each match (libname.table_name).

In other words, i'm looking for a given variable without knowing in which library or table it is located.

thanks in advance

[Reeza]

Use the SASHELP.VCOLUMN table. It has a list of all the data sets and the variables in them.

This would return all entries with the variable Age, including the library, data set name and the variable type.

data want;
set sashelp.vcolumn;

where upcase(name) = 'AGE';

keep libname memname name type;
run;

---

@brulard wrote:

hi,

If someone could suggest an approach to solve the following:

-From multiple libraries;

-scan each table from each library; 

- Flag if find variable 'ID_Number';

-Return location of each match (libname.table_name).

In other words, i'm looking for a given variable without knowing in which library or table it is located.

thanks in advance

---

[brulard]

thanks for pointing me in the right direction, running the code now

[brulard]

... so the query ended after 3 1/2 hour. I unfortunately received repeated ERROR messages (related to different hadoop schemas?). I will consult with our IT team to learn more.

Not sure if is related to my needing to instead code the query in sql. Thanks Reeza

Error message: ERROR: java.sql.SQLException: Could not establish connection to
jdbc:hive2://sdpsvrwm8888.xxxxx.ad.xxxxxxx.com:8443/cmstr;ssl=true;sslTrustStore=/sa
shome/hadoop/gateway.jks;trustStorePassword=xxxxxx?hive.server2.transport.mode=http;hive.ser
ver2.thrift.http.path=gateway/default/hive: HTTP Response code: 500

[Reeza]

Yeah, the dictionary tables are cumbersome when connected to databases. 

You can try PROC DATASETS/PROC CONTENTS instead but you may have to do a loop over all your libraries. If you only have a few that's fine but if you have a lot that's just a little more work. 

[ballardw]

You might check with you DB Admin (or google ) to see which tools the database supports and use PASSTHROUGH SQL instead of SAS code to use the tools. Most databases have some equivalent of dictionary tables. So you would need to know which tables and the names of the variables to look at.

Though "library" starts getting a tad fuzzy with multiple remote databases.