Accessing DBMS Data with the Pass-Through Facility Retrieving DBMS Data with a Pass-Through Query 265
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 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.
Output 14.1 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 a PROC SQL view by
adding a CREATE VIEW statement to the query, removing the ORDER BY clause from
the CONNECTION TO component, and adding the ORDER BY clause to a separate
SELECT statement that prints only the new PROC SQL view. *
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;
* 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.
