Locking of SAS dataset from multiple processes

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Locking of SAS dataset from multiple processes

Hello everyone.  I have multiple sas codes that need to be able to modify the same data-set at the same time.

One process might take 10-15 seconds to update the dataset, while the other takes 5-7 seconds.  however if process A is running, and then process B tries to run, Process B fails on it's update.

I would like to be able to have some piece of base code that would allow me to check if the Dataset is currently being modified by anything else, and if so to wait for X seconds, and then check again.

I tried the code below which I found in a PDF, however it did not work and whenever I run it my BASE sas just crashes (shuts down).

%MACRO ACCESS ;

      %LET   ERR= 99 ;

      %DO %WHILE(&ERR>4) ;

            PROC SORT DATA=LIB.DAT1 OUT=LIB.DAT1;

                BY   KEY ;

           RUN;

           %LET   ERR=&SYSERR ;

     %END;

%MEND;

%ACCESS ;

Does anyone know how I would accomplish this goal without making this into a sql tabl, and then doing update / insert statements with isolation level set to "serializable"?

Thanks all


Accepted Solutions
Solution
‎01-29-2015 05:39 PM
Super User
Posts: 3,250

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

What operating system is SAS running on? If it is Windows, then check out the FILELOCKWAIT option on the LIBNAME statement. If you set this option for longer than the number of seconds your dataset update takes, then the process waiting for access will not time out with an error.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

I'm a little concerned about your first statement but:

Have you looked at WAITFOR?

Solution
‎01-29-2015 05:39 PM
Super User
Posts: 3,250

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

What operating system is SAS running on? If it is Windows, then check out the FILELOCKWAIT option on the LIBNAME statement. If you set this option for longer than the number of seconds your dataset update takes, then the process waiting for access will not time out with an error.

Super Contributor
Posts: 418

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

Heya Ballardw.  I am concerned with the statement as well, but it results from a process that is pretty out of my control sadly.  Very very long story on this one, but I promise I wouldn't be doing this if I had a choice.. (first time i've had to do it in over 4 years of work, and is related to a client request that I had to bend too).

Ballardw.  How would this option help in this example? (i'm not trying to be rude I'm really struggling to understand it).  Say I have a code that is running, and another completely independent code that I don't control is updating a table on a network path. 

What would my code check to "wait for?" since I don't know what code is updating the dataset?

SASKiwi thank you I will look into the FILELOCKWAIT. I wasn't aware of this option.

Super User
Posts: 11,343

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

Your base code would look something like:

systask command "sas myprog1.sas" taskname=sas1;

waitfor sas1 timeout=30; /* waits upto 30 seconds for the program statements is myprog1.sas to finish*/

systask command "sas myprog2.sas" taskname=sas2;

waitfor sas2 timeout=30; /* waits upto 30 seconds for the program statements is myprog2.sas to finish*/systask command "sas myprog3.sas" taskname=sas3;

But if a single location isn't starting the tasks I don't this will work.

Super User
Super User
Posts: 7,039

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

Do you have SAS/Share licensed?

SAS/SHARE

Super Contributor
Posts: 418

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

Hello Tom.  We only have Base SAS, and nothing else sadly.

Super User
Posts: 5,424

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

This is 2015, and building your own locking logic in 4GL can't be the best option.

It must be better to invest in SAS/SHARE, or SAS/ACCESSS to a cheap/free RDBMS that support row level locking.

What does your applications consist of? How big/important is this simultaneous update compared with the whole?

Data never sleeps
Respected Advisor
Posts: 4,173

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

If you're on SAS 9.4 then filelockwait would provide what you're after - else I would have the table in a data base which allows for concurrent access.


There is a Lex Jansen paper which proposes a way to control concurrent write access to a SAS table without SAS/Share http://www.lexjansen.com/pharmasug/2005/posters/po33.pdf  but I never managed to implement this 100% "fool proof"  

Super Contributor
Posts: 418

Re: Locking of SAS dataset from multiple processes

Posted in reply to Anotherdream

Master

.  My solution originally was to do basically what you said. instead of permanently saving a sas dataset, I transitioned it until a sql server database table which has row level locking.  Can I ask what you mean when you saying "writing your own locking logic in 4GL".  I'm not sure what that sentence means (what is 4GL?)

However I really really hate this solution, for a lot of reasons.  Mainly because it implies that my local sas session has to talk to the server the database is on, which can be in different parts of the country (and in my case are.) therefore a task that should take ~20 seconds takes ~10 minutes to go over my companies Network.  Obviously this is information that you couldn't know and given that I really like your solution!

However I originally thought there was no other way to do this without SAS/SHARE.  And I don't disagree that having SAS/SHARE is a good idea, but unfortunately I don't control the budget for my company.  It was hard enough to convince them to bring in BASE SAS, let alone more things with it. Like a lot of companies, SSIS is the golden ticket at mine.

I do appreciate your help tho!

I was not aware of this option, and this is EXACTLY what I needed. Thank you so much!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 324 views
  • 3 likes
  • 6 in conversation