SASTRACE= System Option

Syntax Description

',,,d'

specifies that all SQL statements sent to the Microsoft Jet engine are sent to the log. These statements include the following:

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. The information, however, will vary 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 log.

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.  

The SASTRACE= options have 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 will show you statements like SELECT or COMMIT as the DBMS processes them for the SAS application. The following details will help you manage SASTRACE= output for your files:

• When using SASTRACE= on PC platforms, you must also specify SASTRACELOC= System Option.

• To turn SAS tracing off, specify the following option:

  options sastrace=off;

• Log output is much easier to read if you specify NOSTSUFFIX. For example:

   options sastrace=',,,d' sastraceloc=saslog nostsuffix;

The following example uses SASTRACE =', , , d' so that SQL statements can be reviewed.

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;

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

Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.