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
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
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
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
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
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
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
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.