Friday, August 5, 2016

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

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.