Previous Page | Next Page

SAS Data Files

Understanding an Audit Trail


Definition of an Audit Trail

The audit trail is an optional SAS file that you can create in order to log modifications to a SAS data file. Each time an observation is added, deleted, or updated, information is written to the audit trail about who made the modification, what was modified, and when.

Many businesses and organizations require an audit trail for security reasons. The audit trail maintains historical information about the data, which gives you the opportunity to develop usage statistics and patterns. The historical information enables you to track individual pieces of data from the moment they enter the data file to the time they leave.

The audit trail is also the only facility in SAS that stores observations from failed append operations and that were rejected by integrity constraints. (The integrity constraints feature is described in Understanding Integrity Constraints.) The audit trail enables you to write a DATA step to extract the failed or rejected observations, use information describing why the observations failed to correct them, and then reapply the observations to the data file.


Audit Trail Description

The audit trail is created by the default Base SAS engine and has the same libref and member name as the data file, but has a data set type of AUDIT. It replicates the variables in the data file and additionally stores two types of audit variables:

The _AT*_ variables are described in the following table.

_AT* Variables
_AT*_ Variable Description
_ATDATETIME_ Stores the date and time of a modification
_ATUSERID_ Stores the logon user ID that is associated with a modification
_ATOBSNO_ Stores the observation number that is affected by the modification, except when REUSE=YES (because the observation number is always 0)
_ATRETURNCODE_ Stores the event return code
_ATMESSAGE_ Stores the SAS log message at the time of the modification
_ATOPCODE_ Stores a code that describes the type of modification

The _ATOPCODE_ values are listed in the following table.

_ATOPCODE_ Values
Code Modification
AL Auditing is resumed
AS Auditing is suspended
DA Added data record image
DD Deleted data record image
DR Before-update record image
DW After-update record image
EA Observation add failed
ED Observation delete failed
EU Observation update failed

The type of entries stored in the audit trail, along with their corresponding _ATOPCODE_ values, are determined by the options specified in the LOG statement when the audit trail is initiated. Note that if the LOG statement is omitted when the audit trail is initiated, the default behavior is to log all images.

The user variable is a variable that associates data values with the data file without making them part of the data file. That is, the data values are stored in the audit file, but you update them in the data file like any other variable. You might want to define a user variable to enable end users to enter a reason for each update.

User variables are defined at audit trail initiation with the USER_VAR statement. For example, the following code initiates an audit trail and creates a user variable REASON_CODE for data file MYLIB.SALES:

proc datasets lib=mylib;
  audit sales;
     initiate;
     user_var reason_code $ 20;
run;

After the audit trail is initiated, SAS retrieves the user variables from the audit trail and displays them when the data file is opened for update. You can enter data values for the user variables as you would for any data variable. The data values are saved to the audit trail as each observation is saved. (In applications that save observations as you scroll through them, it might appear that the data values have disappeared.) The user variables are not available when the data file is opened for browsing or printing. However, to rename a user variable or modify its attributes, you modify the data file, not the audit file. The following example uses PROC DATASETS to rename the user variable:

proc datasets lib=mylib;
   modify sales;
     rename reason_code =  Reason;
   run;
quit;

You must also define attributes such as format and informat in the data file with PROC DATASETS. If you define user variables, you must store values in them for the variables to be meaningful.

A data file can have one audit file, and the audit file must reside in the same SAS library as the data file.


Operation in a Shared Environment

The audit trail operates similarly in local and remote environments. The only difference for applications and users networking with SAS/CONNECT and SAS/SHARE is that the audit trail logs events when the observation is written to permanent storage; that is, when the data is written to the remote SAS session or server. Therefore, the time that the transaction is logged might be different from the user's SAS session.


Performance Implications

Because each update to the data file is also written to the audit file, the audit trail can negatively impact system performance. You might want to consider suspending the audit trail for large, regularly scheduled batch updates. Note that the audit variables are unavailable when the audit trail is suspended.


Preservation by Other Operations

The audit trail is not recommended for data files that are copied, moved, sorted in place, replaced, or transferred to another operating environment, because those operations do not preserve the audit trail. In a copy operation on the same host, you can preserve the data file and audit trail by renaming them using the generation data sets feature; however, logging stops because neither the auditing process nor the generation data sets feature saves the source program that caused the replacement. For more information about generation data sets, see Understanding Generation Data Sets.


Programming Considerations

For data files whose audit file contains user variables, the variable list is different when browsing and updating the data file. The user variables are selected for update but not for browsing. You should be aware of this difference when you are developing your own full-screen applications.


Other Considerations

Data values that are entered for user variables are not stored in the audit trail for delete operations.

If the audit file becomes damaged, you cannot process the data file until you terminate the audit trail. Then you can initiate a new audit trail or process the data file without one. To terminate the audit trail for a generation data set, use the GENNUM= data set option in the AUDIT statement. You cannot initiate an audit trail for a generation data set.

In indexed data sets, the fast-append feature can cause some observations to be written to the audit trail twice, first with a DA operation code and then with an EA operation code. The observations with EA represent the observations rejected by index restrictions. For more information, see Appending to an Indexed Data Set in the PROC DATASETS APPEND statement documentation in Base SAS Procedures Guide.


Initiating an Audit Trail

You initiate an audit trail in the DATASETS procedure with the AUDIT statement. Consult the PROC DATASETS AUDIT Statement in Base SAS Procedures Guide for syntax information.

The audit file uses the SAS password assigned to its associated data file; therefore, it is recommended that the data file have an ALTER password. An ALTER-level password restricts read and edit access to SAS files. If a password other than ALTER is used, or no password is used, the software generates a warning message that the files are not protected from accidental update or deletion.


Controlling the Audit Trail

Once active, you can suspend and resume logging, and terminate (delete) the audit trail. The syntax for controlling the audit trail is described in the PROC DATASETS AUDIT statement documentation. Note that replacing the associated data file also deletes the audit trail.


Reading and Determining the Status of the Audit Trail

The audit trail is read-only. You can read the audit trail with any component of SAS that reads a data set. To refer to the audit trail, use the TYPE= data set option. For example, to view the contents of the audit trail, issue the following statement. Note that the parentheses around the TYPE= option are required.

proc contents data=mylib.sales (type=audit);
run;

The PROC CONTENTS output is shown below. Notice that the listing contains all of the variables from the corresponding data file, the _AT*_ variables, and the user variable.

PROC CONTENTS Listing for Data File MYLIB.SALES

                                     
                                     The CONTENTS Procedure

       Data Set Name        MYLIB.SALES.AUDIT                    Observations          0
       Member Type          AUDIT                                Variables             10
       Engine               V9                                   Indexes               0
       Created              12:34 Wednesday, January 24, 2007    Observation Length    111
       Last Modified        12:34 Wednesday, January 24, 2007    Deleted Observations  0
       Protection                                                Compressed            NO
       Data Set Type        AUDIT                                Sorted                NO
       Label
       Data Representation  WINDOWS
       Encoding             Default


                               Engine/Host Dependent Information

               Data Set Page Size          4096
               Number of Data Set Pages    1
               First Data Page             1
               Max Obs per Page            33
               Obs in First Data Page      0
               Number of Data Set Repairs  0
               File Name                   C:\My Documents\myfiles\sales.sas7baud
               Release Created             9.2000A0
               Host Created                XP_PRO


                           Alphabetic List of Variables and Attributes

                        #    Variable          Type    Len    Format

                        5    _ATDATETIME_      Num       8    DATETIME19.
                       10    _ATMESSAGE_       Char      8
                        6    _ATOBSNO_         Num       8
                        9    _ATOPCODE_        Char      2
                        7    _ATRETURNCODE_    Num       8
                        8    _ATUSERID_        Char     32
                        2    invoice           Num       8
                        1    product           Char      9
                        4    reason_code       Char     20
                        3    renewal           Num       8

You can also use your favorite reporting tool, such as PROC REPORT or PROC TABULATE, on the audit trail.


Audit Trails and CEDA Processing

When a SAS data file requires processing with CEDA, audit trails are not supported. For example, if you transfer a SAS data file with an initiated audit trail from one operating environment such as Windows to a different operating environment such as UNIX, CEDA translates the file for you, but the audit trail is not available. For information on CEDA processing, see Processing Data Using Cross-Environment Data Access (CEDA).

The MIGRATE procedure preserves audit trails when migrating data files. For more information, see the MIGRATE Procedure in Base SAS Procedures Guide. The CPORT and CIMPORT procedures preserve audit trails when transporting SAS data files from one operating environment to another operating environment. The CPORT procedure makes a copy of the data file in a transportable format, and the CIMPORT procedure reads the transport file and creates a new host-specific copy of the data file. For more information, see the CPORT Procedure and CIMPORT Procedure in Base SAS Procedures Guide.

CAUTION:
If your data files contain audit trails, do not use your operating environment commands to copy, move, or delete your data files.   [cautionend]

Examples of Using Audit Trails


Example of Initiating an Audit Trail

The following example shows the data and code that are used to create and initiate an audit trail for the data file MYLIB.SALES that is used in earlier examples in this section. MYLIB.SALES stores fictional invoice and renewal figures for SAS products. The audit trail records all events and stores one user variable, REASON_CODE, for users to enter a reason for the update.

Subsequent examples illustrate the effect of a data file update on the audit trail and how to use audit variables to capture observations that are rejected by integrity constraints. The system option LINESIZE is set in advance for the integrity constraints example. A large LINESIZE value is recommended to display the content of the _ATMESSAGE_ variable. The output examples have been modified to fit on the page.

options linesize=250;
   /*------------------------------------*/
   /* Create SALES data set.             */
   /*------------------------------------*/

data mylib.sales;
  length product  $9;
  input product invoice renewal;
datalines;
FSP        1270.00        570
SAS        1650.00        850
STAT       570.00         0
STAT       970.82         600
OR         239.36         0
SAS        7478.71        1100
SAS        800.00         800
;


   /*----------------------------------*/
   /* Create an audit trail with a     */
   /* user variable.                   */
   /*----------------------------------*/

proc datasets lib=mylib nolist;
  audit sales;
    initiate;
    user_var reason_code $ 20;
run;


Example of a Data File Update

The following example inserts an observation into MYLIB.SALES.DATA and prints the update data in the MYLIB.SALES.AUDIT.

   /*----------------------------------*/
   /* Do an update.                    */
   /*----------------------------------*/
 proc sql;
   insert into mylib.sales
       set product = 'AUDIT',
           invoice = 2000,
           renewal = 970,
       reason_code = "Add new product";
quit;

   /*----------------------------------------*/
   /* Print the audit trail. */
   /*----------------------------------------*/
proc sql;
  select product,
         reason_code,
         _atopcode_,
         _atuserid_ format=$6.,
         _atdatetime_
         from mylib.sales(type=audit);
quit;

Updated Data in MYLIB.SALES.AUDIT

                               The SAS System          
                                                                                         
product    reason_code           _ATOPCODE_  _ATUSERID_         _ATDATETIME_ 
________________________________________________________________________________ 
AUDIT      Add new product       DA          xxxxxx       31OCT2007:11:24:32

Example of Using the Audit Trail to Capture Rejected Observations

The following example adds integrity constraints to MYLIB.SALES.DATA and records observations that are rejected as a result of the integrity constraints in MYLIB.SALES.AUDIT. For more information about integrity constraints, see Understanding Integrity Constraints.

   /*----------------------------------*/
   /* Create integrity constraints.    */
   /*----------------------------------*/
proc datasets lib=mylib;
   modify sales;
   ic create null_renewal = not null (invoice)
             message = "Invoice must have a value.";
   ic create invoice_amt = check (where=((invoice > 0) and
               (renewal <= invoice)))
             message = "Invoice and/or renewal are invalid.";
run;

   /*----------------------------------*/
   /* Do some updates.                 */
   /*----------------------------------*/
 proc sql; /* this update works */
    update mylib.sales
      set invoice = invoice * .9,
      reason_code = "10% price cut"
      where renewal > 800;

 proc sql;  /* this update fails */
    insert into mylib.sales
       set product = ''AUDIT',
           renewal = 970,
       reason_code = "Add new product";

 proc sql;  /* this update works */
    insert into mylib.sales
       set product = 'AUDIT',
           invoice = 10000,
           renewal = 970,
       reason_code = "Add new product";

proc sql;  /* this update fails */
    insert into mylib.sales
       set product = 'AUDIT',
           invoice = 100,
           renewal = 970,
       reason_code = "Add new product";
 quit;

   /*----------------------------------------*/
   /* Print the audit trail. */
   /*----------------------------------------*/
proc print data=mylib.sales(type=audit);
  format _atuserid_ $6.;
  var product reason_code _atopcode_ _atuserid_ _atdatetime_;
title  'Contents of the Audit Trail';
run;

   /*----------------------------------------*/
   /* Print the rejected records.            */
   /*----------------------------------------*/
proc print data=mylib.sales(type=audit);
  where _atopcode_ eq "EA";
  format _atmessage_ $250.;
  var product invoice renewal _atmessage_ ;
title  'Rejected Records';
run;

Contents of MYLIB.SALES.AUDIT After an Update with Integrity Constraints shows the contents of MYLIB.SALES.AUDIT after several updates of MYLIB.SALES.DATA were attempted. Integrity constraints were added to the file, then updates were attempted. Rejected Records on the Audit Trail prints information about the rejected observations on the audit trail.

Contents of MYLIB.SALES.AUDIT After an Update with Integrity Constraints

                          Contents of the Audit Trail                                  
                                                                
 Obs    product      reason_code      _ATOPCODE_    _ATUSERID_           _ATDATETIME_

   1     AUDIT     Add new product        DA          xxxxxx       22JAN2007:12:43:27
   2     SAS                              DR          xxxxxx       22JAN2007:13:02:02
   3     SAS       10% price cut          DW          xxxxxx       22JAN2007:13:02:02
   4     SAS                              DR          xxxxxx       22JAN2007:13:02:02
   5     SAS       10% price cut          DW          xxxxxx       22JAN2007:13:02:02
   6     AUDIT                            DR          xxxxxx       22JAN2007:13:02:02
   7     AUDIT     10% price cut          DW          xxxxxx       22JAN2007:13:02:02
   8     AUDIT     Add new product        EA          xxxxxx       22JAN2007:13:02:02
   9     AUDIT     Add new product        DA          xxxxxx       22JAN2007:13:02:02
  10     AUDIT     Add new product        EA          xxxxxx       22JAN2007:13:02:02

Rejected Records on the Audit Trail

                          Rejected Records     
    Obs    product    invoice    renewal    _ATMESSAGE_      
    1     AUDIT         .        970      ERROR: Invoice must have a value. Add/Update 
                                          failed for data set MYLIB.SALES because data
                                          value(s) do not comply with integrity constraint
                                          null_renewal. 
    2     AUDIT       100        970      ERROR: Invoice and/or renewal are invalid.
                                          Add/update failed for data set MYLIB.SALES
                                          because data value(s)a do not comply with
                                          integrity constraint invoice_amt.

Previous Page | Next Page | Top of Page