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.