Suppose you are an Oracle SQL analyst, and you want to pull data from your Oracle database into SAS. You can use the SQL pass-through facility to send DBMS-specific SQL statements directly to Oracle for execution. The syntax executes as if you are coding inside Oracle, and processing occurs within Oracle. Once the Oracle query completes execution, the data is sent to SAS for processing.

With the pass-through facility, you can perform a native SQL query inside a DBMS using PROC SQL. To accomplish this, perform the following tasks inside the SQL procedure:

The CONNECT statement establishes a connection to send DBMS-specific SQL statements to the DBMS or to retrieve DBMS data. The connection remains in effect until you can issue a DISCONNECT statement or terminate the SQL procedure.

The CONNECT statement establishes a connection with the DBMS. Depending on your DBMS setup, you might have to specify specific DBMS connection arguments.

Once you connect to the DBMS, you can submit SELECT or EXECUTE statements.

The DISCONNECT statement ends the connection with the DBMS. If you do not include the DISCONNECT statement, SAS performs an implicit DISCONNECT when PROC SQL terminates. The SQL procedure continues to execute until you submit a QUIT statement, another SAS procedure, or a DATA step. Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement executes.

This example connects to Oracle and performs a simple query on the Customers table.

proc sql;
   connect to oracle (user=User password=Student827 path=localhost);
   select *
      from connection to oracle
      (select * from customers
       where customer like '1%');
   disconnect from oracle;
quit;

When you are using the SQL pass-through facility, there are a few advantages and disadvantages to keep in mind. The DBMS optimizes data summarization, ordering tables, joining, and querying because DBMS SQL code is executed within the DBMS. Any DBMS-specific functions and features can be used. You can also combine SAS features with DBMS-specific features within your query. For example, you can use SAS labels and formats with DBMS-specific features. The results of your query can be saved as a SAS data file or SAS view.

Only the SQL code that is within the parentheses is passed to the DBMS. If you are returning results that contain an ORDER BY clause outside the DBMS-specific SQL query, the ordering of the table occurs in SAS. This also applies to labels and formats. If you specify WHERE processing or sorting within SAS step (for example, as an SQL view), the SQL view executes the instructions within the DBMS. However, the results that are returned are executed by SAS.

If you are familiar with the LIBNAME statement, recall that it enables you to assign a library reference, or an alias, to a SAS library. The syntax for the LIBNAME statement is the keyword LIBNAME, followed by the library reference name, the location of your SAS data in quotation marks, and some options. When you name a library reference, the name can be between one and eight characters long. The name must begin with a letter or an underscore, and it can continue with any number of characters, letters, or numbers and underscores up to eight.

For example, suppose your company uses Teradata, and you need to connect to the database.

When you are using the SAS/ACCESS LIBNAME statement, it enables you to make a connection to Teradata. It establishes a library reference that acts as an alias, or a nickname, to Teradata. This enables you to use the Teradata tables with the SAS syntax, where you reference the table name as a two-level name. It also enables you to use the Teradata table in order to update the table, if you have the correct authority to do so. The SAS/ACCESS LIBNAME statement also enables you to use SAS/ACCESS LIBNAME options to specify how Teradata objects are processed by SAS. Last, it enables you to customize how to connect to Teradata.

When you are using PROC SQL with the SAS/ACCESS LIBNAME engine connection to a database, the engine converts PROC SQL syntax to the native SQL of the database wherever possible.

When you are reading data from a specific database, each SAS/ACCESS LIBNAME statement provides an engine name as part of the LIBNAME statement syntax.

The LIBNAME statement enables you to assign a library reference, or an alias, to a database. To use the SAS/ACCESS LIBNAME statement, specify a libref, engine-name, and necessary connection options to connect to the database.

You can submit a LIBNAME statement with the CLEAR option to release the DBMS and associated resources. It is a good practice to close the DBMS connection when you end your session.

LIBNAME libref clear;