BookmarkSubscribeRSS Feed
brulard
Pyrite | Level 9

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

5 REPLIES 5
Reeza
Super User

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
Pyrite | Level 9

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

brulard
Pyrite | Level 9

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

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 627 views
  • 1 like
  • 3 in conversation