What Is The SQL Pass-Through Facility, And What Are The Advantages Of Using It

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.

The purpose of this section is to provide general information about the SQL Procedure Pass-Through Facility.

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.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.