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)
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.