Tracking Changes in SAS Datasets: SAS Audit Trail

SAS Audit trails are crucial in maintaining the integrity and accuracy of data. In the field of SAS programming, audit trails provide a systematic record of all modifications and updates made to SAS datasets, allowing programmers and data managers to track changes and identify potential errors or inconsistencies in data.

In this blog post, we will explore the importance of audit trails in SAS programming and discuss some best practices for implementing them.

What is an Audit Trail?

An audit trail is a systematic record of all modifications made to a dataset. It typically includes information such as the date and time of the modification, the user who made the modification, and a description of the modification.

Audit trails are critical for maintaining the integrity of data. They allow programmers and data managers to track changes and identify potential errors or inconsistencies in data. They also provide a historical record of the dataset, which can be useful for compliance and regulatory purposes.

Initiate audit trails

You initiate an audit trail using the DATASETS procedure with the AUDIT and INITIATE statements.

The following DATA step creates a sample from the data set sasshelp.shoes The DATASETS procedure adds an audit trail.

proc surveyselect data=sashelp.shoes
    out=sasdsn.shoes( keep=region product stores sales alter=changepw)
    seed=1234
    sampsize=6;
run;

proc datasets lib=sasdsn;
audit shoes (alter=changepw);
initiate;
user_var _reason_change_ $30;
quit; 

1. The ALTER= data set option protects against altering an audit trail.
2. The AUDIT statement of PROC DATASETS initiates and controls event logging to an audit file.
3. The USER_VAR statement defines the user variable _reason_change_ for additional logging. When a user updates the SAS data set, they will use the _reason_change_ variable to provide a reason for their update.
4. An ALTER= or PW= password is recommended for a data set that has an audit trail.

For more information on Password protecting SAS datasets, see our guide on Password Protecting SAS datasets.

The SAS audit trail facility is a read-only SAS data set that contains information about changes made to another SAS data set. The observations in the audit trail data set contain all the variables from the original data set plus the following additional variables.

To refer to the audit trail, use the TYPE= data set option. For example, issue the following statement to view the contents of the audit trail.

proc contents data=sasdsn.shoes(type=audit);
run;

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

Now let’s modify the dataset. We run the code below to modify the
shoes data set. The first data step will modify the number of stores to 10 for the Canada region.

We will insert a new record into the dataset next. _reason_change_ is set to the reason for the modification. You can refer back to the audit table in the audit trail by viewing the audit table.

data sasdsn.shoes;
 modify sasdsn.shoes(alter=changepw);
 where region = "Canada";
 stores = 10;
 _reason_change_ = "Stores corrected";
run; 

proc sql;
insert into sasdsn.shoes(alter=chnagepw)
set Region = "Asia",
 Product = "Sport Shoes",
 Stores = 17,
 Sales = 17347,
 _reason_change_ = "New Region";
quit;

Here is the PROC PRINT output of the audit trail for sasdsn.shoes that shows selected variables from the appended observation along with _reason_change_, _AT*_ variables.

The first record has a _ATOPCODE_ value of DR. The DR code means that this record is the record image before an update has occurred.

The second record contains the _ATOPCODE_ value of DW. The DW means that this is the record after the update.

This record also contains the reason_change we set in the modify data step. The third record contains a _ATOPCODE_ value of DA. The DA code means that this record was added to the data set.

Recover records using Audit Trails

Records that have been changed or deleted can only be recovered from backups or shadow copies. You can easily restore original records using audit trails without contacting your backup administrator.

This is how it can be done.

In the previous example, we already modified Canada Stores from 12 to 10.

Now we can restore his original record back from the audit trail. We first create a transaction data set named rollbackupdate from the audit trail by running the code below.

The rollbackupdate data set will contain the record image before it was updated.

data rollbackupdate;
 set sasdsn.shoes(type=audit);
 where region = "Canada" and _ATOPCODE_ = "DR" and _ATUSERID_= "&sysuserid";
run; 
proc print;

Now we are ready to modify the master data set (sasdsn.shoes) with the transaction data set (rollbackupdate) we just created.

We run the code below to modify the master data set with the transaction data set.

data sasdsn.shoes;
 modify sasdsn.shoes(alter=changepw) rollbackupdate;
 by REGION;
run; 

Before and After Event Logging using Audit Trails

This example modifies the sasdsn.shoes data set to demonstrate before-event and after-event logging.

data sasdsn.shoes;
   modify sasdsn.shoes(alter=changepw);                         
   if region = 'Asia' then
      do;
         stores = 15;
         _reason_change_ = 'data entry correction';   
      end;
   else _reason_change_ = 'no change';                  
run;  
proc print data=sasdsn.shoes(type=audit) noobs;   

1. The sasdsn.shoes data set is specified for modification.
2. The IF statement selects observations to correct. The number of stores is changed from 17 to 15. The _reason_change_ indicates data entry correction.
3. For observations that are not corrected, the _reason_change_ indicates no change.

The audit trail shows several new events.

• The DATA step IF-THEN/ELSE logic caused SAS to read and write all three observations, even though some of the observations did not meet the IF condition.
• The DR code indicates data was read, and a before-event record image was captured.
• The DW code indicates that data was written, and an after-event record image was captured.

Audit Trail to Capture Rejected Observations

In this example, PROC DATASETS adds an integrity constraint to sasdsn.shoes.

proc datasets lib=sasdsn;
   modify shoes(alter=changepw);
   ic create null_product = not null (product)
             message = "Product cannot be blank";
   ic create sales_amt = check (where=((sales >= 0))) 
             message = "Product and/or Sales are invalid.";
run;

In the next step, the SQL procedure attempts to insert an observation that fails the integrity constraint. The failed observation and the custom error message are captured in sasdsn.shoes.audit.

/*This update works*/
proc sql;
insert into sasdsn.shoes(alter=chnagepw)
set Region = "Asia",
 Product = "Boot",
 Stores = 3,
 Sales = 10654,
 _reason_change_ = "New Product added";
quit;

/*This update failes*/
proc sql;
insert into sasdsn.shoes(alter=chnagepw)
set Region = "Asia",
 Stores = 3,
 Sales = 789,
 _reason_change_ = "New Product added";

proc print data=sasdsn.shoes(type=audit);
  format _atuserid_ $6.;
  var region product sales _reason_change_ _atopcode_ _atdatetime_;
title  'Contents of the Audit Trail';
run;
quit;

This proc print prints the information about the rejected observations on the audit trail.

proc print data=sasdsn.shoes(type=audit);
  where _atopcode_ eq "EA";
  format _atmessage_ $250.;
  var region product sales _atmessage_ ;
title  'Rejected Records';
run;

Modify User Variable

You can modify the attributes of a user variable without reinitializing the audit trail.

The following example uses PROC DATASETS to change the length of a user variable and to rename it.

proc datasets lib=sasdsn nolist;
   modify shoes (alter=changepw);
     format _reason_change_ $100.;
     rename _reason_change_=Reason;
quit;

Suspend, Resume, or Terminate an Audit Trail

The following PROC DATASETS suspends and resumes the audit trail.

/*Suspend Audit Trail*/
proc datasets library=sasdsn nolist;
   audit shoes (alter=changepw);
   suspend;
run;
/*Resume Audit Trail*/
proc datasets library=sasdsn nolist;
   audit shoes (alter=changepw);
   resume;
quit;

proc print data=sasdsn.shoes (type=audit) noobs; 
   where _atopcode_ in ("AL","AS"); 
   var _atdatetime_ _atopcode_ _atmessage_;
run;

Suspending or resuming an audit trial is an administrative event that is logged in the audit trail.

• When the audit trail is suspended, the _ATOPCODE_ is AS and the _ATMESSAGE_ is SUSPEND.
• When the audit trail is resumed, the _ATOPCODE_ is AL and the _ATMESSAGE_ is RESUME.

The following PROC DATASETS terminates and deletes the audit trail.

proc datasets library=saslib nolist;
   audit shoes (alter=changepw);
   terminate;
quit;