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.
No comments:
Post a Comment