Capture library connection options into macro variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Capture library connection options into macro variables

Is there a way to capture options of libraries (that are already assigned) into macro variables?

 

The reason that I am asking is that we are working with SAS on a Teradata environment. We have different environment (Dev, Test, etc.) and the code should be portable between the environments. Several libraries are assigned during initialization using properties from the metadata with libname statements for Teradata. The database and schemas are specific for each environment.

LIBNAME  td_lib teradata DATABASE='xxx' SCHEMA='yyy' AUTHDOMAIN='zzz'; 

 

 

Usually code is referring to these libraries (using regular proc sql) and we don't have to worry about the schema or database to which the library is referring. However, in some cases we want to use Teradata specific syntax using the execute (...) by-statement. Before this statement we need to issue a connect-statement with the same database and schema as a particular libref.

proc sql;
   connect to teradata (database='xxx' schema='yyy' authdomain='zzz');
   execute(create table casetest(x char(28)) ) by teradata;
   execute(insert into casetest values('Case Insensitivity Desired') ) by teradata;
quit;

 

 

Is there a way to capture the database and schema used for a particular libref and reuse it in the connect-statement?


Accepted Solutions
Solution
‎02-03-2016 05:25 AM
Super User
Super User
Posts: 7,942

Re: Capture library connection options into macro variables

You can also try looking through the V... views in SASHELP library.  They are views which contain the various metadata elements of the SAS system.  Libnames for instance have their location stored in VSLIB, but I don't have a db connection to test where that info is stored.

View solution in original post


All Replies
Solution
‎02-03-2016 05:25 AM
Super User
Super User
Posts: 7,942

Re: Capture library connection options into macro variables

You can also try looking through the V... views in SASHELP library.  They are views which contain the various metadata elements of the SAS system.  Libnames for instance have their location stored in VSLIB, but I don't have a db connection to test where that info is stored.

Occasional Contributor
Posts: 5

Re: Capture library connection options into macro variables

Thanks for your replies. Both methods (using dictionaries or SASHELP.VLIBNAM) work and provide similar output.

 

The schema is contained in the column sysvalue where sysname equals 'Schema/User'.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 275 views
  • 0 likes
  • 2 in conversation