Enterprise Guide Locks Excel File

[Michael_W]

EG can be absolutely maddening at times.   I run the following PROC IMPORT statement ...

PROC IMPORT DATAFILE="F:\SAS\ACT002A\RA\ESP\Change Control Tables\Revenue and Enrollment\Enrollment Segs to Exclude-&BenYr_..xlsx"
OUT=WRKMBR.CCT_Segs2ExclA (WHERE=(PPI EQ '1' AND Authorized_By NE 'Dummy') RENAME=(IssuerEnrolleeID=SubID_)) DBMS=XLSX REPLACE;
SHEET="Excluded Segments" ;
QUIT;

It runs successfully ...

 NOTE: The import data set has 0 observations and 9 variables.
NOTE: WRKMBR.CCT_SEGS2EXCLA data set was successfully created.

But when I go to open or delete outside of EG, it says it's still in use.  I am 100% confident that the file is not open my me or anyone else.  Windows won't open it because my EG still has a lock on it.   The only solution (albeit a horrible one) is to shut down EG entirely and then it will get released.    

Any idea of how to fix this issue without shutting down my EG session?

[Tom]

If you only ran that code then I doubt that Enterprise Guide itself would even touch that XLSX file.  That is SAS code you showed.  So only the SAS process would be trying to open the XLSX file, not the Windows application Enterprise Guide that you used to create and submit the SAS code.

[Michael_W]

Of course it is SAS code. I submitted it through a code node in EG. Something (EG, SAS) is locking up the file. No SAS processes are running while I am in EG and I cannot open or delete the file in Windows, but as soon as I close EG, I can delete it with no problem. What SAS process do you suppose is running?

[Tom]

You can probably test if it is SAS that has the lock without having to shut down Enterprise Guide.  Just close your connection to the SAS server that EG is using. Or restart the SAS connection.

You could try making a copy of the file and then running PROC IMPORT on the copy and see if that prevents the lock from being made.

filename original "F:\SAS\ACT002A\RA\ESP\Change Control Tables\Revenue and Enrollment\Enrollment Segs to Exclude-&BenYr_..xlsx"
   recfm=n;
filename copy "%sysfunc(pathname(work))\xxx.xlsx" recfm=n;
%let rc=%sysfunc(fcopy(original,copy));
filename original;
proc import datafile=copy dbms=xlsx .....

[Michael_W]

I tried making a copy of the Excel  file and then re-running the Proc Import statement against the copy .. and that works.  But it still doesn't free up the original file;  plus it doesn't solve my problem.  I have not yet tried to close my connection to the SAS server (I assume that you mean the SASApp server), but I will try that the next time it happens.   I will have to re-run all of my LIBNAME statement, but maybe that will work.   If so, it should be significantly better than what I have been doing .. which is saving and shutting down my EG session.   To add insult to injury, it doesn't happen all of the time either.    Thanks for the advice!

[Tom]

You might try moving the subsetting to a separate step instead of using WHERE= dataset option on the output dataset name.  Perhaps the extra complexity is contributing to the problem.

[SASKiwi]

What OS does your SAS server run on? I see you are using a Windows-type folder share to access the spreadsheet from the SAS server. The fact that it is intermittent makes me suspect this is not a SAS issue. In any case SAS uses OS file locking, not its own. 

[AlanC]

The best way, IMO, to find processes that are locking a file is to use process explorer. It can be found on the Microsoft site and is bundled in sysinternals package. It works great on a local file. For remote files, you will probably need access to the system where the process is hosed.

There are lots and lots of resources on the use of sysinternals. It is very well established toolkit that has been around since the 90s. I have even seen college courses on its toolset. Give that a try if you can get access to the mid-tier machine.

Link: Process Explorer - Sysinternals | Microsoft Learn

I download SysInternals suite as one of my first downloads to any new machine. Nothing needs to be installed. Lots of industry staples in it like procmon, procexp, etc.

[Kurt_Bremser]

The use of the F: drive letter makes me suspect that a network share is involved, and that share may not propagate the release of the file handle properly.

[Michael_W]

For us, the "F: drive" is the logical partition on the SASApp server.  The same thing occurs (when it occurs) when I am pointing to the fully qualified URL/path.      I am relatively certain that the issue is with something EG is going to make Windows OS think that the file is still in use (when it's not).   What I am trying to figure out is how to free or release whatever connection EG has to it without restarting EG completely.    I am sure that it's a specific combination of actions that trigger it, I am just sure what they are to replicate them. 

[Reeza]

I think @Kurt_Bremser is probably right. EG is releasing the file, but if you have any sort of network blips, the network doesn't receive the release and it stays locked and SAS isn't checking to ensure the release is dropped.  

Maybe for the other SAS guru's, is there a way to open the file as read only or to explicitly release the lock, as with FCLOSE()?

[Michael_W]

Network blips that only effect EG and its use of or connection to PC Files (specifically Excel)? You say that EG is releasing the file (as if EG is doing its part to release the connection to the file) and that it's something with the network .. yet the file is released as soon as EG is shut down? How is that not EG? I feel this is like one of those situations where you purchase a warranty for a car and then when you go to put a warranty claim in, they insist that the issue is not a covered part or in this case, it's not with the car period .. it's the road that is the problem. I do appreciate the support/effort though. I definitely like your last contribution "is there a way to open the file as read only or to explicitly release the lock, as with FCLOSE()" .. that's the kind of thing I am looking for!

[SASKiwi · Accepted solution]

I suggest you open a track with SAS Tech Support. We use PROC IMPORT a lot ourselves with Excel files on a Windows SAS server via EG and I've never encountered your intermittent problem. It would also be worth checking with your IT / SAS admin to see if they have any ideas. 

[Michael_W]

Thank you. I appreciate the advice. We use PROC IMPORT a lot as well and more often than not, it works as advertised. But when it doesn't, it is head-scratching and frustrating. I have checked with our SAS admin and he doesn't have an explanation and his only suggestion to use SAS Management Studio in lieu of EG. When it happens again, I will open a SAS Tech Support track.

[SASKiwi]

We use SMC too running a lot of batch jobs with PROC IMPORTs and it performs exactly like in EG. Of course with batch jobs your SAS session only lasts as long as the job so that is likely to be less problematic.