Getting ERROR :Unable to clear or re-assign the library DATA1 because it is still in use in SAS

Question

Here is the code I'm running and I'm not sure why I'm getting that ERROR.

options symbolgen mlogic;
libname lib11 '/home/userid';

%macro SFTPLoop(ds);
   %global numobs;
   %let dsid = %sysfunc(open(&ds));
   %if &dsid %then %do;
      %let NumObs= %sysfunc(AttrN(&dsid,NObs));
      %If &NumObs>0 %THEN %DO;
         %do %while (%sysfunc(fetch(&dsid)) = 0);  

         %end;
      %end;
      %else %put ERROR:Data set &dset has 0 rows in it.;
      %let rc = %sysfunc(close(&dsid));
   %end;
   %else %put ERROR:open for data set &dset failed - %sysfunc(sysmsg()).;
%mend SFTPLoop;
%SFTPLoop(lib1.data);

16 libname lib1 '/home/userid';

ERROR: Unable to clear or re-assign the library LIB1 because it is still in use. ERROR: Error in the LIBNAME statement.

Answer 1

Try running it in a fresh session. Also ensure if you have it open in a viewer then it is closed. Ensure no other users or processes are using it.

Sometimes code will error at some point and prevent the close() statement from running. When this happens it is necessary to run the close() manually. While developing sometimes it is convenient just to clear any file handles you have open.

You can use a macro like the one below to do so:

%macro close_all_dsid;
  %local i rc;
  %do i=1 %to 1000;
    %let rc=%sysfunc(close(&i));
  %end;
%mend;
%close_all_dsid;

EDIT : I also noticed that you left the 'meat' out of your code. If you are calling some other macros between the open() and close() statements it is possible you are overwriting your value of dsid as you are not declaring your macro variables as local. I'd suggest you at least declare the following at a minimum:

%local dsid rc;

Another explanation is that it's possible that at some point you ran multiple open() statements without running a corresponding close() each time. If you accidentally ran 2 open() statements in a row, on the first occurrence the value of dsid would be assigned a value of 1. The second time it would be assigned a value of 2. If you then run a close() the '2' would be closed, but the '1' would still be open. No matter how many times you then ran an open() and close() the '1' would never be closed unless you manually ran close(1) which is effectively what my code snippet above is doing.

Answer 2

I got the same error today when trying to re-assign a library. Looking back through my log I saw the following (after a proc append):

NOTE: BASE file MYLIB.MYDS.DATA set to record level control because there is at 
least one other open of this file. The append process may take longer to complete.

Running Robert's macro (close_all_dsid) fixed it. The OS was Windows 2008r2 running SAS 9.2 in a Stored Process server session. The append was successful. Will be back if / when I figure it out!

Answer 3

You can kill and restart the session, and then it will work fine.

Answer 4

please add the following code at the beginning of the code:

ods path reset;