Accessing DBMS Data with the SQL Pass-Through Facility

Retrieving DBMS Data with a Pass-Through Query

This section describes how to retrieve DBMS data by using the statements and components of the SQL pass-through facility. The following example, creates a brief listing of the companies who have received invoices, the amount of the invoices, and the dates on which the invoices were sent. This example accesses Oracle data.

First, the code specifies a PROC SQL CONNECT statement to connect to a particular Oracle database that resides on a remote server. It refers to the database with the alias MyDb. Then it lists the columns to select from the Oracle tables in the PROC SQL SELECT clause.

Note: If desired, you can use a column list that follows the table alias, such as as t1(invnum,billedon,amtinus,name) to rename the columns. This is not necessary, however. If you rename the columns by using a column list, you must specify them in the same order in which they appear in the SELECT statement in the Pass-Through query, so that the columns map one-to-one. When you use the new names in the first SELECT statement, you can specify the names in any order. Add the NOLABEL option to the query to display the renamed columns. [cautionend]

The PROC SQL SELECT statement uses a CONNECTION TO component in the FROM clause to retrieve data from the Oracle table. The Pass-Through query (in italics) is enclosed in parentheses and uses Oracle column names. This query joins data from the Invoices and Customers tables by using the BilledTo column, which references the primary key column Customers.Customer. In this Pass-Through query, Oracle can take advantage of its keyed columns to join the data in the most efficient way. Oracle then returns the processed data to SAS.

Note: The order in which processing occurs is not the same as the order of the statements in the example. The first SELECT statement (the PROC SQL query) displays and formats the data that is processed and returned to SAS by the second SELECT statement (the Pass-Through query). [cautionend]

options linesize=120;

proc sql;
connect to oracle as mydb (user=testuser password=testpass);
%put &sqlxmsg;

title 'Brief Data for All Invoices';
   select invnum, name, billedon format=datetime9.,
          amtinus format=dollar20.2
   from connection to mydb
     (select invnum, billedon, amtinus, name
       from invoices, customers
       where invoices.billedto=customers.customer
       order by billedon, invnum);
%put &sqlxmsg;

disconnect from mydb;
quit;

The SAS %PUT statement writes the contents of the &SQLXMSG macro variable to the SAS log so that you can check it for error codes and descriptive information from the SQL pass-through facility. The DISCONNECT statement terminates the Oracle connection and the QUIT statement ends the SQL procedure.

The following output shows the results of the Pass-Through query.

Data Retrieved by a Pass-Through Query

                                      Brief Data for All Invoices
    INVOICENUM  NAME                                                            BILLEDON            AMTINUS
 ------------------------------------------------------------------------------------------------------------- 
         11270  LABORATORIO DE PESQUISAS VETERINARIAS DESIDERIO FINAMOR        05OCT1998         $2,256,870.00
         11271  LONE STAR STATE RESEARCH SUPPLIERS                             05OCT1998        $11,063,836.00
         11273  TWENTY-FIRST CENTURY MATERIALS                                 06OCT1998           $252,148.50
         11276  SANTA CLARA VALLEY TECHNOLOGY SPECIALISTS                      06OCT1998         $1,934,460.00
         11278  UNIVERSITY BIOMEDICAL MATERIALS                                06OCT1998         $1,400,825.00
         11280  LABORATORIO DE PESQUISAS VETERINARIAS DESIDERIO FINAMOR        07OCT1998         $2,256,870.00
         11282  TWENTY-FIRST CENTURY MATERIALS                                 07OCT1998           $252,148.50
         11285  INSTITUTO DE BIOLOGIA Y MEDICINA NUCLEAR                       10OCT1998         $2,256,870.00
         11286  RESEARCH OUTFITTERS                                            10OCT1998        $11,063,836.00
         11287  GREAT LAKES LABORATORY EQUIPMENT MANUFACTURERS                 11OCT1998           $252,148.50
         12051  LABORATORIO DE PESQUISAS VETERINARIAS DESIDERIO FINAMOR        02NOV1998         $2,256,870.00
         12102  LONE STAR STATE RESEARCH SUPPLIERS                             17NOV1998        $11,063,836.00
         12263  TWENTY-FIRST CENTURY MATERIALS                                 05DEC1998           $252,148.50
         12468  UNIVERSITY BIOMEDICAL MATERIALS                                24DEC1998         $1,400,825.00
         12476  INSTITUTO DE BIOLOGIA Y MEDICINA NUCLEAR                       24DEC1998         $2,256,870.00
         12478  GREAT LAKES LABORATORY EQUIPMENT MANUFACTURERS                 24DEC1998           $252,148.50
         12471  LABORATORIO DE PESQUISAS VETERINARIAS DESIDERIO FINAMOR        27DEC1998         $2,256,870.00

The following example changes the Pass-Through query into an SQL view. It adds a CREATE VIEW statement to the query, removes the ORDER BY clause from the CONNECTION TO component, and adds the ORDER BY clause to a separate SELECT statement that prints only the new SQL view. (footnote 1)

libname samples 'your-SAS-data-library';

proc sql;
connect to oracle as mydb (user=testuser password=testpass);
%put &sqlxmsg;

   create view samples.brief as
     select invnum, name, billedon format=datetime9.,
            amtinus format=dollar20.2
         from connection to mydb
            (select invnum, billedon, amtinus, name
               from invoices, customers
               where invoices.billedto=customers.customer);
%put &sqlxmsg;

disconnect from mydb;

options ls=120 label;

title 'Brief Data for All Invoices';
select * from samples.brief
   order by billedon, invnum;

quit;

The output from the Samples.Brief view is the same as shown in Data Retrieved by a Pass-Through Query.

When an SQL view is created from a Pass-Through query, the query's DBMS connection information is stored with the view. Therefore, when you reference the SQL view in a SAS program, you automatically connect to the correct database, and you retrieve the most current data in the DBMS tables.

FOOTNOTE 1: If you have data that is usually sorted, it is more efficient to keep the ORDER BY clause in the Pass-Through query and let the DBMS sort the data.

Top of Page