Chapter Contents

Previous

Next

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 Procedure Pass-Through Facility to access DBMS data. The Pass-Through Facility uses the SAS/ACCESS interface view engine to read and write data between the SAS System and the DBMS. See SQL Procedure's Interaction with SAS/ACCESS Software for detailed information.

Note:   It is recommended that you use the new SAS/ACCESS LIBNAME statement to access your DBMS data more directly and to take full advantage of Version 7 and Version 8 enhancements. See SAS/ACCESS LIBNAME Statement for more information about the new LIBNAME statement.  

In the following example, you want just a brief listing of the companies to whom you have sent invoices, the amount of the invoices, and the dates on which the invoices were sent. This example accesses ORACLE data.

First, you specify a PROC SQL CONNECT statement to connect to a particular ORACLE database that resides on a remote server. You refer to the database with the alias MYDB.

Then you list the columns that you want 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; however, this is not necessary. If you choose to 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.  

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 INVOICE 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; it then returns the processed data to the SAS System.

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 the SAS System by the second SELECT statement (the Pass-Through query).  

options linesize=120;

proc sql;
connect to oracle as mydb (user=scott orapw=tiger path='myorapath');
%put &sqlxmsg;

title 'Brief Data for All Invoices';
   select invoicenum, name, billedon format=datetime9.,
   amountinus format=dollar20.2
   from connection to mydb
     (select *
      
       from invoice, customers
       where invoice.billedto=customers.customer
       order by billedon, invoicenum);
%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 PROC SQL Pass-Through Facility. (See Macro Variables and System Options for more information.) The DISCONNECT statement terminates the ORACLE connection, and QUIT ends the SQL procedure. Data Retrieved by a Pass-Through Query shows the results of the Pass-Through query.

Data Retrieved by a Pass-Through Query

Brief Data for All Invoices INVOICENUM NAME billedon amountinus ------------------------------------------------------------------------------------------------------------- 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

To change the Pass-Through query into a PROC SQL view, you add a PROC SQL CREATE VIEW statement to the query. You also remove the ORDER BY clause from the CONNECTION TO component and add it to a separate SELECT statement that prints only the new PROC SQL view. Generally, it is more efficient to sort data only when needed by the program.(footnote 1)

libname slib 'Your-SAS-data-library';

proc sql;
connect to oracle as mydb (user=scott orapw=tiger path='myorapath');
%put &sqlxmsg;

   create view slib.brief as
     select invoicenum, name billedon format=datetime9.,
     format=dollar20.2
         from connection to mydb
            (select *
               from invoice, customers
               where invoice.billedto=customers.customer);
%put &sqlxmsg;

disconnect from mydb;

options ls=120;

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

quit;

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

When a PROC 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 PROC SQL view in a SAS program, you automatically connect to the correct database, and you retrieve the most current data in the DBMS tables.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.