Design Help Needed: Audit trail for multiple-use macro without contention/locking

[Bruno7]

I need to capture SAS macro variable values that are passed into a multi-use SAS macro that is used by hundreds of SAS jobs at the same time, all day long, 24/7/365, and write them to a common audit trail location so that we know what variable values folks are passing in to the macro to be able to create reports for decision-making for leadership.

How can I write out the variable values to a common location without contention/locking being an issue, AND without writing to an external database? 

A SAS dataset seems out of the question because of locking.  Is there a way to change a SAS dataset's properties to be shared, like we used to set DISP=SHR in JCL on mainframe datasets so that multiple users can write to the dataset at the same time?

I did think of a PROC APPEND, only because of the performance being faster since it doesn't have to read in all the data from the base dataset, but still, with hundreds of jobs hitting the macro all at once,  I still think there is a risk of performance issues for our jobs.

A third idea I thought of was to add a txt file write to the macro that would jsut dump the macro variable values to its own txt file in a folder location.  The, to run reports I would read in the hundreds - thousands - of txt files, grabbing the values and writing them to a single SAS dataset for reporting.  It would work but seems clunky/messy, but if that's the only way to do this without adding a database write to the macro (which I also think would have contention issues, due to the heavy and constant use this macro gets.)

Any ideas?  (not an option for running jobs in succession as they come from many different teams from all over the company.)

[Patrick]

If I remember right from long ago then DISP=SHR doesn't give you concurrent access but just avoids running into filelock issues. If so then libname option FILELOCKWAIT gives you a very similar behavior. 

Write access to the SAS table is still sequential per job so you need to write a process that doesn't lock the table for too long. Proc Append should be o.k.

The other option would be to create job specific SAS tables. Like <fixed name component>_<generated name component>

For analysis you then just combine the individual tables like:
data want; set <libref>.<fixed name component>:; run;

[SASKiwi]

My recollection is that to update the SAS data library you need to set DISP=OLD to gain exclusive access. That will necessarily prevent any other users accessing this library at the same time. Also the FILELOCKWAIT option that can be used to pause LIBNAME allocations until they become free is only available on Windows and Unix.

Do you have access to any external databases that you could use to store this data in like DB2 as they have multi-user updating baked in?

[Tom]

On UNIX I have used a line like this for years without any issues.

systask command
 "echo %sysfunc(date(),yymmddn),%sysfunc(time(),time8),&sysuserid,&sysver,&sysscp,&syshostname >>&initlog"
 nowait shell
;

You can periodically rename the file to keep it from getting too big, but still keep the history.

[Bruno7]

Idea: Is there a way to turn an audit trail on a dataset to know which rows are being read/selected? I know there is a way to turn on audit trail functionality to capture what rows are added, deleted, and changed.....but how about simply READ?