There are some new SAS In-Database processing features available with version 9.2. In addition to the explicit pass-through syntax with Proc SQL option, SAS has expanded its capacity to generate DBMS-specific or “native” SQL for Proc SQL and even non-SQL procedures to do more processing within the native databases and minimize I/O. Seven non-SQL procedures are now supported in Oracle and DB2, including Proc Freq, Proc Means, Proc Summary, Proc Tabulate, Proc Report, Proc Rank, and Proc Sort (additional procs are supported in Teradata). System options to enable additional output to the log are SASTRACE (see previous post) and SASTRACELOC. Use system options DIRECT_SQL and SQLGENERATION to enable and disable in-database processing and try comparing log output with and without in-database processing.
Tag Archives: sastraceloc
SAS System Options: SASTRACE
You can use the SAS system option SASTRACE to see where your SAS code is being processed when using implicit pass-through (with the libname statement vs. explicit pass-through syntax) to query a relational database. PC users must also use SASTRACELOC with SASTRACE to show trace results in the log. Even if your SAS code is not written using Proc SQL, SAS will still try to translate some of it and pass it through to the native database for processing.
The following statement with SASTRACE set to ‘,,,d’ ensures that SQL SELECT, INSERT, UPDATE, CREATE, DROP, and DELETE statements sent to the Microsoft Jet engine are identified and printed to the log:
OPTIONS SASTRACE=’,,,d’ SASTRACELOC=SASLOG NOSTSUFFIX;