topic Re: Lock SAS dataset in SAS Programming

Lock SAS dataset

helloSAS — Fri, 21 Jun 2013 16:53:39 GMT

Hi,

Is there a way I can lock SAS dataset so that users cannot open it physically in PC SAS or SAS EG and yet be accessed by the SAS programs?

I have an issue where users keep SAS dataset open and our programs trying to write to the same file. Hence the job errors out. Looking for a way to fix this.

Thanks!

Re: Lock SAS dataset

Ksharp — Sat, 22 Jun 2013 01:55:49 GMT

Yes. add a password to that table.

check dataset option pw=

or proc datasets

Re: Lock SAS dataset

Doc_Duke — Mon, 24 Jun 2013 13:09:35 GMT

I think that what helloSAS is trying to do is to allow the PC SAS and EG users to run programs against the dataset, but not open it in spreadsheet view. The password won't resolve that because it applies to both. Perhaps helloSAS can clarify the need.

Re: Lock SAS dataset

helloSAS — Mon, 24 Jun 2013 13:29:10 GMT

I want the datasets to be locked to open it in a spreadsheet view. However the SAS jobs should have it available to read/write.

Hope this helps.

Re: Lock SAS dataset

Doc_Duke — Mon, 24 Jun 2013 19:15:08 GMT

That's what I was afraid of. To my knowledge, there is no SAS tool that could do that.

If you have sufficient administrative rights, you could kill the PC SAS and EG sessions. That will be unlikely to win you any friends among the analysts...

A less brutish approach would be to maintain two copies of the data; one that you can update and another that they can read. Then you could use system tools to copy the files when the readers are inactive. Task scheduler can re-try every x-minutes until successful. If the users complain about stale data, you can get them to use peer pressure on the offending analyst(s).

Re: Lock SAS dataset

ChrisNZ — Tue, 25 Jun 2013 11:38:30 GMT

Or if that's an important feature, you could use sas/share.

It won't allow you to control which bit of sas opens the table and change the rights accordingly, but you can regain control of the datasets without killing sessions.

The problem you have is common btw.

A common way to deal with it is to kill all sas sessions at night unless they have a "save me" flag of some sort (like a specific file in the work directory) so that the nightly data refresh can take place.

Another method is that when a refresh fails, an automatic email is sent by the refresh job informing users that their data is not there, sorry, with an explanation.

The explanation can include the fact that xyz had the table open...

Re: Lock SAS dataset

Amir — Tue, 25 Jun 2013 12:06:09 GMT

Hi,

Would creating views of the data which the users could access help?

Regards,

Amir.

Re: Lock SAS dataset

SASKiwi — Wed, 26 Jun 2013 20:29:12 GMT

You might also find it useful to read through the other forum posts on this topic as it pops up frequently - search on the word lock.

While there are a number of workarounds and tricks to deal with this problem, my own personal view is that SAS should implement a more robust solution that doesn't involve buying additional software like SAS\SHARE.

However I do appreciate that this is complex issue as anyone who is aware of how relational databases handle table/row locking will concur.