Dataset with Multiple Users Appending - Missing Data

[cbrotz]

We have a SAS dataset that multiple SAS users append to on a regular basis.  For some reason, it is missing data quite frequently and I need to do a file restore back to the most recent version.  Has anyone had this happen with a file that is frequently appended to?  It is the only file I am seeing issues with so I am thinking it has something to do with multiple users appending.

[AlanC]

A SAS dataset is not designed to be used in a shared configuration like this. It is a simple, physical file on a disk. Reading is probably fine but writing is bad. You are probably seeing file locks happening.

Find a storage mechanism that handles concurrent access such as a DBMSs (ex. SQL Server).

[SASKiwi]

SAS datasets are not designed to be updated at the same time by different users. The user who gets a lock on the dataset first will be able to append, but others trying to append at the same time will fail with log errors. You could try experimenting with the FILELOCKWAIT LIBNAME option, but you would have to ensure that ALL users apply the option when needing to append the dataset.

[cbrotz]

What if we had each user append to their own SAS file and ran an overnight job that did the final append to the main table? 

[AlanC]

That should be fine. It is very simple to use a DB as well. You need SAS/Access to ODBC/OleDB/etc. but a db is optimal here.

[SASKiwi]

That sounds like a good solution as long as users don't need to see other users appends during the same day.