Hack 3.11 Exploiting In-Database Processing with Oracle

SAS Programming Professionals,

Did you know that you can have Oracle do the heavy lifting when running SAS programs that process Oracle tables?

Over the past few years, SAS has been working with major DBMS vendors (Sybase and Oracle, to name a couple) to facilitate “in-database procedures”.  In-database procedures allow the native DBMS to perform particular SAS procedures within the database itself and output the result sets to SAS.  In-database procedures can reduce the elapsed time of SAS programs and reduce network bandwidth because large amounts of data are crunched within a DBMS by its native engines, and smaller result sets are served back to your SAS programs. 

Several SAS procedures, including PROC FREQ, PROC SUMMARY, and PROC TABULATE have been instrumented to perform in-database processing of Oracle tables.  You can direct SAS to use in-database processing via the SQLGENERATION=DBMS option.  Here is an example:

libname oracledb ORACLE user='ProdUser' orapw='PROD$427' path='Oracledb' schema='ledzep';

options sqlgeneration = dbms;

proc options option=sqlgeneration;

run;

proc summary data=oracledb.tblBIGDATA ;

      class STATE_CODE;

      var   ADDRESS_ID CREATED_BY ENV_HAZARD_FLAG MODIFIED_BY VALID_ADDRESS_FLAG;

      output out=work.summ1 sum=;

      run;

In the example, the LIBNAME statement allocates a production Oracle data base.  The OPTIONS statement, SQLGENERATION=DBMS, specifies that we want to have in-database processing of SAS procedures whenever possible.  The OPTIONS procedure simply dumps the current value of SQLGENERATION to the SAS log so we are doubly-sure we are good-to-go.  The SUMMARY procedure accesses the tblBIGDATA Oracle table, summarizing specific variables and storing the result set in the SUMM1 SAS data set in the Work SAS data library.  Oracle performs the summarization within the database and passes the SUMM1 data set to SAS.  The large DBMS table stays put in the Oracle database, and we get a small summarized SAS data set.  Not bad, eh?

If you are doing a lot of work processing large Oracle tables, consider how in-database procedures might be of benefit to your SAS programs.

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.