SASTRACE

Generates trace information from a DBMS engine.

Valid in:	OPTIONS statement, configuration file, SAS invocation
Default:	None

Syntax

Details

Example

Syntax

SASTRACE=',,,d' | ' ,,d,' | ' d,' | ',,,s'

Syntax Description

',,,d'

specifies that all of these SQL statements sent to the Microsoft Jet engine are sent to the SAS log:

SELECT	INSERT
UPDATE	DROP
CREATE	DELETE

',,d,': specifies that all routine calls are sent to the log. When this option is selected, all function enters and exits, as well as pertinent parameters and return codes, are traced. However, the information varies from engine to engine.

This option is most useful if you are having a problem and need to send a SAS log to technical support for troubleshooting.

'd,': specifies that all OLE DB API calls for connection information, column bindings, column error information, and row processing are sent to the log. This option is most useful if you are having a problem and need to send a SAS log to technical support for troubleshooting.

',,,s': specifies that a summary of timing information for calls made to the DBMS is sent to the SAS log.

Details

The SASTRACE= option has behavior that is specific to SAS/ACCESS software. SASTRACE= is a very powerful tool to use when you want to see the commands that the SAS/ACCESS engine sends. SASTRACE= output is DBMS-specific. However, most SAS/ACCESS engines show statements like SELECT or COMMIT as the DBMS processes them for the SAS application. These details can help you manage SASTRACE= output for your files:

When using SASTRACE= on PC platforms, you must also specify SASTRACELOC= options, as described in SASTRACELOC.
To turn SAS tracing off, specify this option:
```
options sastrace=off;
```
Log output is much easier to read if you specify NOSTSUFFIX. Here is an example:
```
 OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;
```

Note: By default, Microsoft Access and Microsoft Excel LIBNAME engines use ROWSET_INSERT instead of executing the SQL INSERT command. You do not see the INSERT statement in the trace log when inserting rows into a table. You can use the LIBNAME statement or the SQL_INSERT=YES option to see the INSERT statement in the trace log.

Example: Review SQL Statements

This example specifies SASTRACE =', , , d' so that SQL statements are sent to the SAS log.

DATA work.winter_birthdays;
   INPUT empid birthdat DATE9. lastname $18.;
   FORMAT birthdat DATE9.;
DATALINES;
678999 28DEC1966 PAVEO             JULIANA        3451
456788 12JAN1977 SHIPTON           TIFFANY        3468
890123 20FEB1973 THORSTAD          EDVARD         3329
;
RUN;
OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog nostsuffix;
LIBNAME mydblib 'c:\sasdemo\demo.mdb' ;
PROC DELETE DATA=mydblib.snow_birthdays; RUN;
DATA  mydblib.snow_birthdays;
   SET work.winter_birthdays;
RUN;
PROC PRINT DATA=mydblib.snow_birthdays;
 RUN;
LIBNAME mydblib CLEAR;

Here is the SQL statements in the SAS log output.

1    DATA WORK.winter_birthdays;
2       INPUT empid birthdat date9. lastname $18.;
3       FORMAT birthdat DATE9.;
4    DATALINES;
NOTE: The data set WORK.WINTER_BIRTHDAYS has 3 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      CPU time            0.04 seconds
8    ;
9   RUN;
10
11   OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;
12   LIBNAME mydblib 'c:\sasdemo\demo.mdb' ;
NOTE: Libref MYDBLIB was successfully assigned as follows:
      Engine:        ACCESS
      Physical Name: c:\sasdemo\demo.mdb
13
14   proc delete data=mydblib.snow_birthdays; RUN;
Jet_0: Executed:
DROP  TABLE `snow_birthdays`
NOTE: Deleting MYDBLIB.snow_birthdays (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.01 seconds
      CPU time            0.00 seconds
15
16   DATA mydblib.snow_birthdays;
17      SET work.winter_birthdays;
18   RUN;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
Jet_1: Executed:
CREATE TABLE `snow_birthdays` (`empid` Double,`birthdat` DateTime,`lastname`
   VarChar(18))
NOTE: There were 3 observations read from the data set WORK.WINTER_BIRTHDAYS.
NOTE: The data set MYDBLIB.snow_birthdays has 3 observations and 3 variables.
NOTE: Successfully Inserted 3 row(s)
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      CPU time            0.01 seconds
19
20  LIBNAME mydblib clear;
NOTE: Libref MYDBLIB has been deassigned.