BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Anotherdream
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

9 REPLIES 9
ballardw
Super User

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

Have you looked at WAITFOR?

SASKiwi
PROC Star

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.

Anotherdream
Quartz | Level 8

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.

ballardw
Super User

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.

Tom
Super User Tom
Super User

Do you have SAS/Share licensed?

SAS/SHARE

Anotherdream
Quartz | Level 8

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

LinusH
Tourmaline | Level 20

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
Patrick
Opal | Level 21

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"  

Anotherdream
Quartz | Level 8

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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