Hack 3.16 Keeping Track of SAS Data Set Updates with Audit Trails

SAS Programming Professionals,

Did you know that you can have SAS keep an audit trail of all of the updates made to a SAS data set? 

This is particularly handy if you need to determine who updated a SAS data set at any given time for a security review or a data integrity audit.  The audit trail file allows you to track when particular observations were added, modified, or deleted.  You may even be able to fashion your own update roll-back program from the audit file.

Creating an audit trail file for a SAS data set is easily done via the DATASETS Procedure.  Here is an example:

proc datasets library=cperm noprint;

      audit orsales;

      initiate;

run;

quit;

Executing the code, above, results in a SAS audit file named orsales.sas7baud being built in the CPERM SAS data library.  When updates are made to the orsales SAS data set, entries are made to the attendant orsales SAS audit file.

You can determine the variables available in your audit file by using PROC CONTENTS with the TYPE=AUDIT data set option.  Here is an example:

proc contents data=cperm.orsales(type=audit);

run;

The resulting Alphabetic List of Variables and Attributes looks like this:

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Label
4	Product_Category	Char	25		Product Category
5	Product_Group	Char	25		Product Group
3	Product_Line	Char	20		Product Line
7	Profit	Num	8	12.2	Profit in USD
6	Quantity	Num	8	6.	Number of Items
2	Quarter	Char	6		Quarter
8	Total_Retail_Price	Num	8	12.2	Total Retail Price in USD
1	Year	Num	8	4.	Year
9	_ATDATETIME_	Num	8	DATETIME19.
14	_ATMESSAGE_	Char	8
10	_ATOBSNO_	Num	8
13	_ATOPCODE_	Char	2
11	_ATRETURNCODE_	Num	8
12	_ATUSERID_	Char	32

The variables beginning with _AT are the audit trail variables.  Two of the most important are _ATDATETIME_ and _ATUSERID_ which hold the date/time an observation was updated and the user ID of the person who updated the observation, respectively.

Once a SAS data set with an audit trail is updated, you can examine the specifics of the change by using PROC PRINT with the TYPE=AUDIT data set option:

proc print data=cperm.orsales(type=audit);

run;

You can use other SAS procedures, such as PROC REPORT and PROC SQL to print out the SAS audit file, too.

There is much, much more to discuss on this topic, and the SAS technical writers handle it with their usual thoroughness in the SAS online documentation on support.sas.com.  Check it out!

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.