A Comprehensive Guide to the SQL Pass-Through Facility

The SQL pass-through SAS Facility is an extension to the SQL procedure that enables you to send DBMS-specific statements to a database management system and retrieve DBMS data directly.

To use the SQL Procedure Pass-Through Facility, you must have SAS/ACCESS software because it uses SAS/ACCESS to connect to the DBMS. Thus, when using the SQL Procedure Pass-Through Facility, you specify DBMS syntax, not SAS SQL.

What is the SQL Pass-Through Facility?

The SQL Pass-Through Facility is a tool available in many database management systems and programming languages. It allows you to send SQL statements directly to a specific database system. Rather than relying on the native SQL dialect of the programming language you are using, the Pass-Through Facility sends your SQL queries directly to the database engine. This means that you can make use of the full power of your database system, including its unique features and optimizations, which can help you achieve better results.

PROC SQL will try to pass as much logic as possible to the database, but there are times when it will not be able to.

If SAS functions are used in a query without an equivalent in the database (or in the SAS/ACCESS engine), the query cannot be passed to the database.

The data is pulled into SAS when the query is not fully passed to the database. Here is a case that makes a larger difference than you might realize.

libname odb <database> path=dbserver user=... password=...;
proc sql;
   create table odb.new as
   select * from db.oracledata where flag=1;
quit;

It will pull all the data matching flag=1 from the database and load it back into SAS. As the number of rows increases, the process slows down. In this case, a pass-through would be much faster.

proc sql;
   connect dbase (server=dbserver user=user password=pass);
   execute (create table odb.new as
   select * from db.oracledata where flag=1) as dbase;
   disconnect dbase;
quit;

SQL Procedure Pass-Through Facility is composed of three statements and one component:

• The CONNECT statement establishes a connection with the DBMS.
• The EXECUTE statement sends dynamic, non-query DBMS-specific SQL statements to the DBMS.
• The CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement retrieves data directly from a DBMS.
• The DISCONNECT statement terminates the connection with the DBMS.

Any arguments you specify in the corresponding CONNECT statement are stored whenever you create a PROC SQL view.

Any arguments you specify in the corresponding CONNECT statement are stored whenever you create a PROC SQL view.

A SAS program can use the PROC SQL view to establish the appropriate connection to a DBMS.

The syntax for the SQL Procedure Pass-Through Facility

A SQL procedure pass-through facility is used to send an SQL statement to a DBMS that is DBMS-specific and does not require a query:

PROC SQL;	<CONNECT TO DBMS-name <AS alias><
<(connect-statement-argument-1=value
...<connect-statement-argument-n=value>)>>
<(DBMS-argument-1=value
...<dbms-argument-n=value>)>>;
	EXECUTE (DBMS-SQL-statement)
BY dbms-name|alias;
	<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

Using SAS/ACCESS LIBNAME statements or PROC SQL Pass-Through statements CONNECTION TO, you can connect to a DBMS and query its data:

PROC SQL;	<CONNECT TO DBMS-name <AS alias><
<(connect-statement-argument-1=value
...<connect-statement-argument-n=value>)>>
<(DBMS-argument-1=value
...<dbms-argument-n=value>)>>;
	SELECT column-list	FROM CONNECTION TO dbms-name|alias
(DBMS-query)
optional PROC SQL clauses;

	<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

The following pass-through facility example sends a query to an ORACLE database for processing:

proc sql;
   connect to oracle as myconn (user=smith password=secret 
      path='myoracleserver');

   select * 
      from connection to myconn
         (select empid, lastname, firstname, salary
            from employees
            where salary>75000);

   disconnect from myconn;
quit;

In this example, the CONNECT statement establishes a connection with an ORACLE database using the arguments USER=, PASSWORD=, and PATH=.

The CONNECTION TO component in the FROM clause of the SELECT statement enables data to be retrieved from the database.

The DBMS-specific statement that is sent to ORACLE is enclosed in parentheses. The DISCONNECT statement terminates the connection to ORACLE.

To store the same query in a PROC SQL, use the CREATE VIEW statement:

libname viewlib 'SAS-library';
proc sql;    
   connect to oracle as myconn (user=usr password=pass 
      path='myoracleserver'); 
   create view viewlib.salary as
      select * 
         from connection to myconn       
            (select empid, lastname, firstname, salary          
               from employees          
               where salary>75000);  
   disconnect from myconn; 
quit; 

SQL Procedure Pass-Through Facility Return Codes

An error condition is logged in the SAS log when you use the PROC SQL statements available in the SQL Procedure Pass-Through Facility.

Additionally, the SQL Procedure Pass-Through Facility generates return codes and messages that you can access through SAS macro variables:

SQLXRC – It contains the DBMS return code that identifies the DBMS error.

SQLXMSG – It contains descriptive information about the DBMS error generated by the DBMS.

In the SAS log, the SQLXRC and SQLXMSG macro variables are printed. After executing a SQL Procedure Pass-Through Facility statement, the macro variables SQLXRC and SQLXMSG are reset.

As you can see, the benefits of using SQL pass-through SAS are many. These are just a few of the key points to consider. Ultimately, it is a viable option that SAS programmers should consider repeatedly.