topic Re: Identify given variable from multiple libraries & tables in SAS Programming

Identify given variable from multiple libraries & tables

brulard — Wed, 06 Dec 2017 16:31:32 GMT

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

Re: Identify given variable from multiple libraries & tables

Reeza — Wed, 06 Dec 2017 16:38:57 GMT

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

Re: Identify given variable from multiple libraries & tables

brulard — Wed, 06 Dec 2017 16:50:06 GMT

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

Re: Identify given variable from multiple libraries & tables

brulard — Wed, 06 Dec 2017 20:43:36 GMT

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

Re: Identify given variable from multiple libraries & tables

Reeza — Wed, 06 Dec 2017 21:28:59 GMT

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.

Re: Identify given variable from multiple libraries & tables

ballardw — Wed, 06 Dec 2017 23:28:58 GMT

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.