Analytiks Logo
Back to Tech Corner
SAS Programming

Access Database Directly or Via SAS Metadata Engine

We will work with an example of connecting to an Oracle database (db). In first scenario we will connect to the db directly (using SAS ACCESS/ORACLE) as well as connect to the same db through Metadata engine.

Direct Method

Pros:

  1. As long you have appropriate GRANT in the target db you may access anytime and not have to wait for your sas administrator

Con:

  1. You need to know how to use the Oracle engine (or which ever db you are working with, its engine) and appropriate options to access data.

Code: (read comments)

/* Identify Oracle library containing target Oracle tables */
libname myoralib oracle user=myuser pw=mypw
path=ora_db preserve_tab_names=yes
connection=sharedread schema=myschema;

/* Lists all available Oracle tables */
proc datasets library=myoralib;
quit;

/* Displays the Oracle claims table */
proc print data=myoralib.claims (readbuff=500);
run;

/* for performance use the specified index */
data work.temp;
set myoralib.claims (dbindex=myindex);
run;

Using SAS Meta Engine

Pros:

  1. No need to know how to use ora engine or data access options
  2. All setup and backend services are done by the SAS administrator, usually through SAS management Console, the metadata engine interprets setup and locates data
  3. No need to be aware of how to connect to SAS metadata server, metadata repository, or know connection options and information; provided via system options.

Cons:

  1. Dependency on the SAS administrator to setup and test the connection setup
  2. Dependency on SAS metadata permission, whether the library is displayed and if displayed could be accessed.
  3. Unless METAOUT=DATA ALL is defined in setup, tables, columns are limited to what is defined in repository.
  4. Need to be aware of defined system dsns; published by administrator

Code: (read comments)

/* library= defines info re. oracle lib, connection */
/* information is provided via sys options, meta */
/* engine queries repository, info from library= */
/* is retrieved w connection & schema information */
/* meta engine generates libname like direct method */
libname metaeng meta library=mylib;

/* meta engine uses oracle engine access oracle data */
proc datasets library=metaeng;
quit;

/* meta engine interacts w meta repo for meta associated */
/* with claims table returning only reg. columns in repo */
proc print data=metaeng.claims;
run;

/* table index info is stored in meta library objects */
/* meta engine use of index requests query to the */
/* repository to retrieve index information. The meta */
/* index must match the physical index on the target */
/* table Metadata engine uses index info in repository*/
data work.temp;
set metaeng.claims;
run;
View All Articles