topic How can i retrieve a Library for the path of an ODBC? in SAS Programming

How can i retrieve a Library for the path of an ODBC?

StickyRoll — Wed, 05 Jan 2022 03:34:08 GMT

I have an ODBC connection that connects to Oracle Database.

In SAS, i connect to this ODBC connection with the following statement.

libname staging oracle authdomain="OracleAuth" path='dubaiserver.sas.com';

What I want to do is to not hardcode the username, password, and library path in the code.

However, I am having a hardtime to retrieve the library path here. I can find this path in SAS Management Console by opening the library "staging".

I see some guides that path can easily be resolved. However, the Database path is different from server host.

Is there a way to make the "path" dynamic?

My desired libname statement is:

libname staging oracle authdomain="OracleAuth" path=DBPath;

Re: How can i retrieve a Library for the path of an ODBC?

Patrick — Wed, 05 Jan 2022 04:20:17 GMT

If you have a different database then you would also need a different authdomain.

If you need this library regularly then best define it in SAS metadata - under a different libref though.

What you always can do is point one libref to another. Something like: libname mystg (staging).

The library pre-defined in SAS metadata doesn't need to be pre-assigned. You can always use it via the SAS Meta engine and liburi (and then just assign the libref of your desire).

Re: How can i retrieve a Library for the path of an ODBC?

SASKiwi — Wed, 05 Jan 2022 05:10:23 GMT

If you were using an ODBC connection then you would be using the ODBC engine on your LIBNAME statement. Since you use the ORACLE engine, that means you interface with the Oracle client software installed on your SAS server. The client software contains a file called TNSNAMES.ORA which is where all of the Oracle paths are defined.

Any new database paths first need to be defined in TNSNAMES.ORA. Then you can try them out in a SAS LIBNAME statement. It is best to trial new database connections in a LIBNAME before defining them in SAS metadata.

Re: How can i retrieve a Library for the path of an ODBC?

AllanBowe — Wed, 05 Jan 2022 07:22:30 GMT

If you are looking to extract the library definition from metadata, then this macro will help (and it is configured for Oracle libname engines): https://core.sasjs.io/mm__assigndirectlib_8sas_source.html

We use it in Data Controller for SAS to enable direct connections using the relevant system account.

Here's the extract:

%else %if &engine=ORACLE %then %do;
  %put NOTE: Obtaining &engine library details;
  data _null_;
    length assocuri1 assocuri2 assocuri3 authdomain path schema $256;
    call missing (of _all_);
 
    /* get auth domain */
    rc=metadata_getnasn("&liburi",'LibraryConnection',1,assocuri1);
    rc=metadata_getnasn(assocuri1,'Domain',1,assocuri2);
    rc=metadata_getattr(assocuri2,"Name",authdomain);
    call symputx('authdomain',authdomain,'l');
 
    /* path */
    rc=metadata_getprop(assocuri1,
      'Connection.Oracle.Property.PATH.Name.xmlKey.txt',path);
    call symputx('path',path,'l');
 
    /* schema */
    rc=metadata_getnasn("&liburi",'UsingPackages',1,assocuri3);
    rc=metadata_getattr(assocuri3,'SchemaName',schema);
    call symputx('schema',schema,'l');
  run;
  %put NOTE: Executing the following:/; %put NOTE-;
  %put NOTE- libname &libref ORACLE path=&path schema=&schema;
  %put NOTE-     authdomain=&authdomain;
  %put NOTE-;
  libname &libref ORACLE path=&path schema=&schema authdomain=&authdomain;
%end;