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?
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.
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.
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 .....
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!
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.
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.
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.
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.
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.
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()?
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.
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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.