This section describes
how to retrieve DBMS data by using the statements and components of
the SQL pass-through facility.
This 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. The
example accesses Oracle data.
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
MyDb alias. It then lists the columns to select from the Oracle tables
in the PROC SQL SELECT clause.
If you want, 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.
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 Customers primary key column.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) is displayed 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;
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.
This output shows the
results of the pass-through query.
Data That a Pass-Through Query Retrieves
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.00The next 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.
libname samples 'your-SAS-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;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.