BookmarkSubscribeRSS Feed
CPAZ
Calcite | Level 5

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

8 REPLIES 8
ArtC
Rhodochrosite | Level 12

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

CPAZ
Calcite | Level 5

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

Ksharp
Super User

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

CPAZ
Calcite | Level 5

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      

Ksharp
Super User

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

Anind_Sharma
Calcite | Level 5

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

jakarman
Barite | Level 11

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.  

---->-- ja karman --<-----
Tom
Super User Tom
Super User

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;

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