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.

Hack 3.10 Executing Native DBMS Commands with PROC SQL

SAS Programming Professionals,

Did you know that you can execute native Database Management System (DBMS) commands to manipulate actual database tables via PROC SQL?

The EXECUTE statement in PROC SQL allows you to send native SQL commands to a DBMS such as SQL Server or Oracle to perform various functions.  For instance, consider the following program:

proc sql;

      connect to oledb(provider=sqlOleDb dataSource=sqlservx

       properties=("Integrated Security"=SSPI "Initial
                    Catalog"=Catalog1));

      execute(drop table dbo.oldtabl) by oledb;

      execute(create view dbo.viewprod as select * from dbo.prodtabl)
              by oledb;

      quit;

First, the program executes the CONNECT statement to provide the connection information for accessing the SQL Server database.  Secondly, the program uses an EXECUTE statement to drop a table in the database.  Finally, the second EXECUTE statement creates a database view.  Note that you must make sure that the syntax within the parenthesis following the EXECUTE statement is syntactically correct for the DBMS that you are accessing.  Oh, and do I really need to remind you that you must have the proper SAS/Access product for the particular DBMS you are attempting to access via SAS?

As an added tip, the macro variables &SQLXRC and &SQLXMSG contain any return code and message, respectively, returned by the DBMS.  So, you may want to  programmatically query them after executing PROC SQL.

Best of luck in all your SAS endeavors!

----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.

Hack 3.9 Determining Whether a SAS Transport File was Created by PROC CPORT or PROC COPY

SAS Programming Professionals,

Did you know that you can easily tell the difference between a SAS transport file created by PROC COPY and one created by PROC CPORT?

SAS has two types of transport files:  those created with PROC COPY and those created with PROC CPORT.  Transport files are linear SAS files used to transport SAS data sets between operating systems or between versions of SAS.  You must use PROC COPY to recreate SAS data sets from transport files created by PROC COPY and you must use PROC CIMPORT to recreate SAS data sets created by PROC CIMPORT.  But, what do you do if you are not sure which SAS procedure was used to create a particular transport file?

PROC COPY creates a file whose first 40 characters contain this ASCII text:

        HEADER RECORD*******LIBRARY HEADER RECORD!!!!!!!00

PROC CPORT creates a file whose first 40 characters contain this ASCII text:

**COMPRESSED** **COMPRESSED** **COMPRESSED** **COM

…so you can either peek at the file using a text editor, or run a simple SAS program that dumps the first record of the file.  Consider this example:

/*Create transport file with PROC CPORT*/

filename cportfil "c:\temp\class_cport.xpt" ;

proc cport data=sashelp.class file=cportfil;

run;

/*Create transport file with PROC COPY*/

libname xportout xport "c:\temp\class_copy.xpt";

proc copy in=sashelp out=xportout;

    select class;

run;

/*Dump CPORT transport file */

data _null_;

if _n_ = 1 then do;

    put ;

    put "dump of CPORT transport file";

    put ;

end;

infile cportfil obs=1;

   input theline $ascii80. ;

   put theline;

run;

/*Dump PROC COPY transport file */

filename copyfil "c:\temp\class_copy.xpt";

data _null_;

   if _n_ = 1 then do;

    put ;

    put "dump of COPY transport file";

    put ;

end;

infile copyfil obs=1;

   input theline $ascii80. ;

   put theline;

run;

We first create a SAS transport file using PROC CPORT and then create one with PROC COPY.  Then, we use a DATA _NULL_ step to dump the first observation of the CPORT transport file, followed by one that dumps the first record of the COPY transport file.  The log for the two DATA _NULL_ steps looks in part like this:

20   /*Dump CPORT transport file */

21   data _null_;

22   if _n_ = 1 then do;

23       put ;

24       put "dump of CPORT transport file";

25       put ;

26   end;

27   infile cportfil obs=1;

28      input theline $ascii80. ;

29      put theline;

30   run;

NOTE: The infile CPORTFIL is:

      Filename=c:\temp\class_cport.xpt,

      RECFM=V,LRECL=256,File Size (bytes)=1920,

      Last Modified=04Dec2011:20:43:55,

      Create Time=04Dec2011:20:27:58

dump of CPORT transport file

**COMPRESSED** **COMPRESSED** **COMPRESSED** **COMPRESSED** **COMPRESSED********

32   /*Dump PROC COPY transport file */

33   filename copyfil "c:\temp\class_copy.xpt";

34   data _null_;

35      if _n_ = 1 then do;

36       put ;

37       put "dump of COPY transport file";

38       put ;

39   end;

40   infile copyfil obs=1;

41      input theline $ascii80. ;

42      put theline;

43   run;

NOTE: The infile COPYFIL is:

      Filename=c:\temp\class_copy.xpt,

      RECFM=V,LRECL=256,File Size (bytes)=2080,

      Last Modified=04Dec2011:20:43:55,

      Create Time=04Dec2011:20:27:59

dump of COPY transport file

HEADER RECORD*******LIBRARY HEADER RECORD!!!!!!!000000000000000000000000000000

Note the difference!

Best of luck in all your SAS endeavors!

----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.