Defining Oracle libraries in SAS

Accessing Oracle data bases from SAS can be made really easy if we define libraries that encapsulate the connection parameters, so we can access them in a really easy manner. If the database is already configured in your TNS file, with a name such as CRM_DWH, we can easily define a library such as this:

/* Defining access to the CRM_DWH database */ 
libname crm_dwh oracle user="user"  password="pass"  path='CRM_DWH';

/* Accessing the table clients */ 
proc sql; 
 select count(*) from crm_dwh.clients; 
quit; 

In this way, we can access all tables that are visible to the user. However, in some cases we would like to access a different schema than the default one. Fortunately, we can easily specify different schemas present in the same database. Let's say we have a SALES schema in our CRM_DWH database, then we can directly access the data just adding a schema option:

/* Defining the access to SALES schema in  the CRM_DWH database */ 
libname crm_dwh oracle user="user"  password="pass"  SCHEMA=SALES  path='CRM_DWH';

/* Obtaining information from the table monthly_sales in the SALES schema */ 
proc sql; 
 select sum(selling_price) from sales.monthly_sales; 
quit; 

In some cases, there could be database services that are not specified in the default TNSNAMES file, in which case we could manually define the parameters to access the Oracle database. This can easily be done by defining the connection parameters in a macro variable, and then we can use it on the library definition as in the previous examples:

/* Defining the Oracle DB service */ 
%LET TNS_CRM_DWH= " 
  (DESCRIPTION = 
    ( ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1532)) ) 
    ( CONNECT_DATA =  (SERVICE_NAME = CRM_DWH ) ) 
  )";

/* Using it to define a library */ 
libname crm_dwh oracle user="user"  password="pass"  SCHEMA=SALES  path=&TNS_CRM_DWH.; 

Acessing oracle databases using libraries can make your life a lot easier when working from SAS, since you can use an uniform approach to work with multiple data sources without the hassle of pass through sentences.