264 Retrieving DBMS Data with a Pass-Through Query Chapter 14
the Sample Data” on page 271 for information about the tables that are used in the
sample code.
Note: Before you rerun an example that updates DBMS data, resubmit the
SampData.sas program to re-create the DBMS tables.
Retrieving DBMS Data with a Pass-Through Query
This section describes how to retrieve DBMS data by using the statements and
components of the Pass-Through Facility. In the following example, you create 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 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).
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;
