topic Re: %sysfunc(open) causes table to be locked if error occurs in SAS Programming

%sysfunc(open) causes table to be locked if error occurs

CPAZ — Fri, 06 Apr 2012 18:07:25 GMT

Hi All,

I have an issue when I open a table using %sysfunc(open(table). I run some code using do loops and if statements, if there is an error before the %sysfunc(close(table) statement, its locks the table and I have to re-start in order to get the table unlocked.

Is there a way to check if the table is locked / unlocked before th open statement, and if it locked, can I unlock it?

Thanks,

Chris

Re: %sysfunc(open) causes table to be locked if error occurs

ArtC — Fri, 06 Apr 2012 19:34:25 GMT

Have you tried looking at &SYSRC or the SYSMSG function? something like the following untested code.

%macro tryit;

%let dsid = %sysfunc(open(work.a));

%if not &dsid %then %do;

%put %sysfunc(sysmsg());

%put &sysrc;

%end;

%if &dsid %then %let rc = %sysfunc(close(&dsid));

%mend tryit;

%tryit

Re: %sysfunc(open) causes table to be locked if error occurs

CPAZ — Fri, 06 Apr 2012 20:29:23 GMT

Hi ArtC,

This did not work and maybe I should give you a little bit more info on this. Here is a snippet of my code:

%macro loop;

%let dsid = %sysfunc(open(work.graph));

%do %while (%sysfunc(fetch(%dsid)) = 0;

%end;

%let rc = %sysfunc(close(&dsid));

Proc sql;

create table work.graph as select

......;run;quit;

%loop

I first outside of the loop create the table graph. I then invoke the loop, it opens the table and performs some processing. If an error occurs inside the loop while the table is open, the next time I run the code I get the following error:

ERROR: You cannot open WORK.GRAPH.DATA for output access with member-level control because

WORK.GRAPH.DATA is in use by you in resource environment IOM ROOT COMP ENV.

I have tried the %sysfunc(close) function before generation of the initial Proc sql but not luck.

Hopefully this may give you a picture of what I am trying to do.

I am running SAS 9.2

Thanks, Chris

Re: %sysfunc(open) causes table to be locked if error occurs

Ksharp — Mon, 09 Apr 2012 04:05:34 GMT

Check LOCK statement and &syslckrc macro variable to see whether some table have been locked.

And use CLEAR to release this lock of table.

LOCK work.graph CLEAR ;

Ksharp

Re: %sysfunc(open) causes table to be locked if error occurs

CPAZ — Mon, 09 Apr 2012 22:01:50 GMT

Thanks for the idea; I placed this code right before I generate the work.graph table and here is the result log:

361 LOCK work.graph CLEAR;

ERROR: WORK.GRAPH.DATA is not locked by you.

362 proc sql;

362 ! /* setting up graph table by parameter */

363 create table graph as select

364 parameter,

365 product,

366 item,

367 &cond_line

SYMBOLGEN: Macro variable COND_LINE resolves to W29 as W29, W30 as W30, W31 as W31, W32 as W32,

W33 as W33, W34 as W34, W35 as W35, W36 as W36, W37 as W37, W38 as W38, W39 as W39,

W40 as W40

368 from work.test9

369 order by item, parameter, product;

ERROR: You cannot open WORK.GRAPH.DATA for output access with member-level control because WORK.GRAPH.DATA is in use by you in resource environment IOM ROOT COMP ENV.

Any other thoughts? Thanks for your help, Chris

Re: %sysfunc(open) causes table to be locked if error occurs

Ksharp — Tue, 10 Apr 2012 03:29:28 GMT

Hi.

Firstly use

lock work.graph list;

Check whether work.graph is locked .

And if you can change the access level of work.graph from member-level to record-level .

create table work.graph(cntllev=rec)

And if you are using EG ? if it were, plz close the current EG and start a new EG -> new code

run the code again,check it out.

http://support.sas.com/kb/17/175.html

Ksharp

Re: %sysfunc(open) causes table to be locked if error occurs

Anind_Sharma — Mon, 18 Aug 2014 10:30:56 GMT

Hi,

If you are using the SAS EG , then this issue can be rectified by using below option.

This will most likely appear when project contains a task that uses as its input table as output table from a prior task.The most likely situation is when there are two or more tasks linked together.

To overcome this issue , Just make sure the dataset is not open or not in edit mode and then

Click on Tools -> Options ->Data -> Data General

Unselect the option 'Automatically Open Data When Added in the project"

This issue is generally fixed in SAS EG 4.2 (base 9.2) but if your server is still based on 9.1 then it can consist the error.

Thanks

Re: %sysfunc(open) causes table to be locked if error occurs

jakarman — Mon, 18 Aug 2014 11:08:57 GMT

The %sysfunc(open ..... Will open a SAS dataset and lock that. It happens by this open function.

The basic logic is rather simple and generic common to multi-users systems. See: SAS(R) 9.3 Functions and CALL Routines: Reference The default locking mode is record-level. This is the same locking level as with viewing/editing a dataset. (Eguide or Base)

The dataset-id you get returned as non-zero value is defined as a unique identifier. Failing to close that one for whatever reason will cause the lock lasting fore all the time. You can expect the locking even to work at OS-level. (not all SAS versions OS-types)

What you could do is: Define that dsid as global and reuse check that as needed.

%global dsid_loop_graph ;

%let dsid_loop_graph = %sysfunc(open(work.graph));

Then check &dsid_loop_graph for zero/nonzero and after successful close reset it to zero.

Design an other macro for the cleanup processing/close.

Running a macro after a datastep has stopped processing by an error is working.

Datasteps/procs are stopped but macro-s are still being processed.

Re: %sysfunc(open) causes table to be locked if error occurs

Tom — Mon, 18 Aug 2014 12:17:32 GMT

Perhaps you need to redesign your program. Why are you using macro language to open the dataset anyway? Can you convert that logic to normal SAS code instead. In practice SAS will normally just assign integers 1,2,3,etc to the file handles. If your SAS sessions is still open then you might just try brute force to close it.

data _null_;

do dsid=1 to 256;

rc=close(dsid);

end;

run;