BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
wellsfargo123
Fluorite | Level 6

One dataset is updated by several sas programs. If the dataset is locked by another process, 

 

i use the macro 

 

%macro trylock(member=, timeout=);
   %local starttime;
   %let starttime = %sysfunc(datetime());
   %do %until (&syslckrc = 0
       or %sysevalf(%sysfunc(datetime()) > (&starttime + &timeout)));
      %put trying to open ...;
      %put trying lock ...;
      lock &member;
      %if &syslckrc ne 0 %then %let rc=%sysfunc(sleep(15));
      %put syslckrc=&syslckrc;
   %end;
%mend trylock;

The dataset I am using is run_st_final, which is being updated by sveral programs.
after calling the above macro,
data _null;
%put &=syslckrc;
if syslckrc=0 then;
proc append base=dtpth.run_st_final data=work.&dtst;
run;
lock dtpth.run_st_final clear;
run;
I am getting an error you did not lock the dataset run_st_final.

My understanding is that the above macron lock the dataset so that I can append.
syslckrc=0 means, I was able to lock the dataset before the next activity.

My understanding is correct?
How can I rectify the error with the loock run_st_final clear.. you didnot lock this dtaset.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@wellsfargo123 wrote:

Right. As per the feedback from the team, in our case, none opened the dataset in EG/Studio. So I was thinking of waiting for the dataset to be freed , if it was being used by some other program.   If the dataset is open on EG/studio , just for reading also causes the lock error ?

 


Because a SAS write operation needs exclusive access to a SAS table also opening a table via EG in read mode will lock the table. Having said that: IF the libname in EG uses filelocks=none then SAS will not put a lock on the table and you still can have another program write to it. This of course can then lead to some unexpected results for the EG session.

Another option would be to create a copy of the table, have your batch programs add data to it and then at the end copy/move the table back to the original location via OS commands that won't be "bothered" if there is some SAS lock on the target file.

If you have multiple batch programs that need exclusive write access to the table then also use filelockwait=... for the libname used by these batch programs.

View solution in original post

11 REPLIES 11
ballardw
Super User

Basically If something else has a lock on the file you can't change it. If it is your process and program then make sure it is completed running but if the lock is because a different user is updating the file you can't.

wellsfargo123
Fluorite | Level 6

Understood. Using the above macro can I make the program to sleep(seconds)  to see whether the lock is  cleared?

SASKiwi
PROC Star

How long does your PROC APPEND take? Personally I find it a lot easier just to rely on the FILELOCKWAIT LIBNAME option where you just set the time to wait longer than the process causing the locking.

wellsfargo123
Fluorite | Level 6

Thanks, I shall test try this.

wellsfargo123
Fluorite | Level 6

From my experience, FILELOCKWAIT LIBNAME  is better solution for programs running in production.

SASKiwi
PROC Star

I agree, but you haven't explained where your PROC APPEND is being used and who is likely to be opening the appended table. If someone opens the table in EG or SAS Studio, then it remains locked as long as they keep it open. If that happens then a lock checking macro won't get around that. 

wellsfargo123
Fluorite | Level 6

Right. As per the feedback from the team, in our case, none opened the dataset in EG/Studio. So I was thinking of waiting for the dataset to be freed , if it was being used by some other program.   If the dataset is open on EG/studio , just for reading also causes the lock error ?

 

 

 

 

Patrick
Opal | Level 21

@wellsfargo123 wrote:

Right. As per the feedback from the team, in our case, none opened the dataset in EG/Studio. So I was thinking of waiting for the dataset to be freed , if it was being used by some other program.   If the dataset is open on EG/studio , just for reading also causes the lock error ?

 


Because a SAS write operation needs exclusive access to a SAS table also opening a table via EG in read mode will lock the table. Having said that: IF the libname in EG uses filelocks=none then SAS will not put a lock on the table and you still can have another program write to it. This of course can then lead to some unexpected results for the EG session.

Another option would be to create a copy of the table, have your batch programs add data to it and then at the end copy/move the table back to the original location via OS commands that won't be "bothered" if there is some SAS lock on the target file.

If you have multiple batch programs that need exclusive write access to the table then also use filelockwait=... for the libname used by these batch programs.

SASKiwi
PROC Star

Note FILELOCKS is only available on Unix or z/OS.

LinusH
Tourmaline | Level 20

From version 8.1, EG has an options that unlocks data sets (in a data grid). I think the default time-out is 3 minutes.

I assume/hope there is similar functionality i SAS Guide, but I couldn't find any information about that.

If you have big (enough) problems with locking, consider using SAS/SHARE or an external RDBMS (such as SQL Server).

Data never sleeps
Kurt_Bremser
Super User

If your SAS session runs on UNIX, you can delete the file (remove the directory entry) even though it is opened by another process. But you have to do the append in WORK for this.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 11 replies
  • 1685 views
  • 0 likes
  • 6 in conversation