Audit column values

[kaziumair]

Hi everyone ,

I have to track changes made to column values . For example , If there is a column Passed with value yes .

Passed

Yes

Now ,If the user updates the value to No , then I want to track the change in value , the user who changed the value and the timestamp when the change occurred.  

Passed	Previous_value	Change_by	timestamp
No	Yes	user1	6/22/21 6:15 PM

I came across the Audit Trail feature , but it creates a separate dataset. I want to add new columns i.e. previous_value , change_by and timestamp to the table being tracked.

Is there a way to achieve this?

[ChrisNZ]

>  I want to add new columns 

That's a very strange way to do things. Do you want to keep adding columns with every change? How do you know it was column PASSED that was changed in your example?

Or is the second table you are showing us a custom-built audit table? If so you are creating a second data set anyway.

[jimbarbour]

By column values, I assume that you mean in a SAS dataset not an external database.  Assuming you have sufficient disk space, you could use the generations feature of SAS to create multiple versions of a SAS dataset.  Every time a SAS job runs that changes the value of a dataset, a new generation is created and the old generation is renamed to include a #nnnn in it's name where nnnn is the generation of the dataset.

What you would need to do periodically is to do some kind of a compare of the current generation with all generations created since the last time you checked for changes.  I think this could be a lot of work, particularly if a lot of generations are created between each check, but you could do it.

Alternatively, you could read the Audit Trail dataset and then base on what the Audit Trail tells you, you probably could create the columns that you're trying to create.  I haven't tried this, but assuming the Audit Trail feature captures the particulars of the changes you're interested in, you should be able to do it.

Jim

[Patrick]

You could also store history in the table using an SCD2 approach (new row per change with a new valid from and eventually valid to date) - or a live and history table where you update/insert into the live table (=1 row per "id") and always also insert a change to the history table (=1 row per "id" and date).