Consultant Frederick Pratter continues his winning streak with this third edition of Web Development with SAS by Example. Web programmers of all levels will appreciate Frederick's many real-world examples and clean delivery. Speaking of delivery, I'd encourage those of you who attend SAS Global Forum and regional user group conferences to seek out Frederick if he's speaking or presenting a paper. Not only is he a long-time SAS expert, but he has the most amazing voice. If we could turn one SAS Press title into an audio book, I'd vote for this one!
Whether you get a chance to meet Frederick or not, this week's featured tip is a stand-out.
The following excerpt is from SAS Press author Frederick Pratter's book "Web Development with SAS by Example, Third Edition" Copyright © 2011, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)
SQL Pass-Through
The least complicated way to manage remote database tables is with PROC SQL. This alternative offers a relatively straightforward interface for experienced SQL users. The main advantage is that SQL statements can be “passed through” to the database engine, where they execute in native mode. Except for bulk loads, where it is still probably better to use DBLOAD, PROC SQL has the advantage of simplicity and familiarity. It is important to note, however, that there are DBMS-specific differences from system to system. Again, the best advice is to consult the documentation that comes with the specific version of the SAS/ACCESS product, and be sure to coordinate
with your DB administrator before accessing tables remotely!
The following example illustrates how to use PROC SQL as an alternative to PROC ACCESS:
Three SQL statements are required: Connect and disconnect attach to the database and detach respectively. The SQL select statement has two parts: the expression enclosed in parentheses (select * from EMP) is “pass-through SQL.” This code is sent to the Oracle database server to return the data from table “emp.”
The outer select * from connection to oracle returns the result to SAS. Finally, the create table clause causes the results to be saved as the temporary data set WORK.EMP. If this clause were omitted, PROC SQL would simply display the table in the output window (the default behavior for a “select”).
As noted previously, to write to an Oracle database, it is possible to use the Oracle SQL*Loader, by specifying
BULKLOAD=YES and appropriate BL_OPTIONS on the output table name in PROC SQL. See “Bulk Loading for Oracle” in the SAS/ACCESS 9.2 for Relational Databases: Reference, 2nd Edition, where an example is shown.
UNIX Server with a Windows Client
In addition to the SAS/ACCESS Interface to DBMS, if the database is on a UNIX server and SAS is running on a Windows client, it is possible to use SAS/ACCESS to ODBC. The main advantage of this approach, as opposed to the DBMS-specific products, is that it allows a Windows client to access a wide variety of database engines, not
just the one it is licensed for.
Read a free chapter from Frederick's book, as well as reviews for it, by visiting his author page.
7 Comments
Can I have a sas dataset and libref in the from clause in an oracle sql pass through?
No, as the pass through SQL runs entirely in the database (Oracle), it can't reference a SAS data set. If the data set is small, you should copy the table into the database first in a separate step, perhaps to a user-specific temp space, and then craft your pass through SQL with all tables being native to Oracle.
Thank you.
Pingback: SAS author’s tip: Configuring TCP security - The SAS Bookshelf
Thanks for the commen Chris. I've found the first approach using "connect to " to work better with large tables (>100M rows). Using a libname for implicit pass-thru seems to have more overhead, although I would have assumed it wouldn't make any difference.
The "connect to" approach is less ambiguous about what's going to run in the database, but the syntax is more cumbersome and requires knowledge of the database connection parameters (including user ID and password), which aren't always available to end users.
Recent versions of SAS (9.2 and 9.3) have improved on the implicit passthrough implementation, and more and more types of queries are delegated to the database without having to pull data into the SAS session unnecessarily.
This is a great example of "explicit pass through". (It's explicit in that you specify a CONNECT directive right in the SQL, not explicit in that it contains words that might be offensive...)
Another method you can use is "implicit pass through", where you define a library to the database using a LIBNAME statement:
And then let SAS' SQL processor handle the job of deciding what work can be pushed to the database process:
proc sql; create table emp as select * from ora.emp T1 /* shorthand alias */ where T1.region = 'EAST' order by T1.dept;In this example, we would hope that the database can filter the table and sort the results before returning the results to SAS, thus saving lots of I/O work within your SAS session. The savings multiple when you have more database-centric work, such as joins and calculated columns that can be handled by the database server.