Identify given variable from multiple libraries & tables

Reply
Frequent Contributor
Posts: 123

Identify given variable from multiple libraries & tables

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

Super User
Posts: 23,296

Re: Identify given variable from multiple libraries & tables

[ Edited ]

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



 

Frequent Contributor
Posts: 123

Re: Identify given variable from multiple libraries & tables

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

Frequent Contributor
Posts: 123

Re: Identify given variable from multiple libraries & tables

... 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

Super User
Posts: 23,296

Re: Identify given variable from multiple libraries & tables

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. 

Super User
Posts: 13,321

Re: Identify given variable from multiple libraries & tables

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.

Ask a Question
Discussion stats
  • 5 replies
  • 112 views
  • 1 like
  • 3 in conversation