SQL Pass-Through and the ODBC Interface

Does SAS implicit SQL pass-through sometimes fail to meet your needs? Do you sometimes need to communicate directly with your Oracle or DB2 database in that database's native language? Explicit SQL pass-through might be your solution. The author briefly introduces syntax for explicit SQL pass-through queries before showing examples of specific situations when explicit pass-through queries solve problems when extracting data. The author discusses the relationship between processing location and processing speed. She also gives specific examples of how differences between Oracle, DB2, and SAS sometimes make it necessary to do the initial extraction or transformation of data via pass-through. The examples used to illustrate the differences between the RDBMS and SAS include numeric precision and naming conventions. A brief discussion of differences in SQL dialects and functions between systems is also included. INTRODUCTION This paper assumes the reader has access to the following products: Base SAS and SAS/Access, which allows SAS to communicate with over 60 different types of data sources outside of SAS (see SAS/Access documentation for a full listing). Several terms related to database connectivity are introduced and defined in this section prior to mentioning them in the body of the paper. A Relational Database Management System (RDBMS) is a collection of normalized data sets where the tables are linked on key fields to store and retrieve data. Common examples include IBM DB2, Microsoft SQL Server, MySQLTM, Sybase, IBM Informix, Teradata, Oracle, and Microsoft Access databases. Each RDBMS has its own native dialect of Structured Query Language (SQL). SQL can also be used within SAS by invoking the SQL Procedure. Open Database Connectivity (ODBC), which was designed to access relational databases using SQL, allows the user to name and configure connections to various data sources using an appropriate driver and then use the data source name to link to tables from within another database system (see screenshot below for an example of where to configure ODBC connections). MS Access, for example, can use ODBC connections to link to SQL Server tables. SAS can also use the ODBC engine to connect to tables in a variety of other types of databases. A SAS user can reference the ODBC connection in a LIBNAME statement to connect to previously configured data sources. More recently, SAS has added an interface to Object Linking and Embedding Database (OLEDB), a successor to ODBC which offers better performance and connectivity to a wider range of data sources. OLEDB is less established than ODBC and outside of the scope of this paper, which focuses on relational data sources (see technote http://support.sas.com/techsup/technote/ts700.pdf and Microsoft’s documentation for more information about OLEDB as well as ftp://ftp.sas.com/techsup/download/v8papers/odbcdb.pdf for a brief comparison of ODBC and OLEDB). SAS/Access also includes direct interfaces to Oracle and DB2 databases, making it possible to connect directly to these types of databases by specifying the path and using the Oracle and DB2 engines instead of the ODBC (or OLEDB) engine. As an alternative to the LIBNAME statement, a user can elect to use the SAS/Access interface to ODBC, Oracle, or DB2 with the SQL Pass-Through facility to directly access data in an RDBMS. A pass-through query processes data in its native environment using native SQL functions instead of having SAS do the work. The remainder of this paper focuses on how, when, and why to use explicit SQL pass-through syntax to access data that is housed in Oracle and DB2 databases. PROC SQL NESUG 2011