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;
ALTER=data set option protects against altering an audit trail.
PROC DATASETSinitiates and controls event logging to an audit file.
- 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.
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.
|_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
DA = Added data record image
DD = Deleted data record image
DR = Before updating the recorded image
DW = After updating the recorded image
EA = Observation add failed
ED = Observation delete failed
EW = Observation update failed
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
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;
sasdsn.shoesdata set is specified for modification.
- The IF statement selects observations to correct. The number of stores is changed from 17 to 15. The
_reason_change_indicates data entry correction.
- For observations that are not corrected, the
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
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
/*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
- When the audit trail is resumed, the
_ATOPCODE_is AL and the
The following PROC DATASETS terminates and deletes the audit trail.
proc datasets library=saslib nolist; audit shoes (alter=changepw); terminate; quit;