topic Re: Enterprise Guide Locks Excel File in SAS Enterprise Guide

Enterprise Guide Locks Excel File

Michael_W — Wed, 24 May 2023 20:38:24 GMT

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?

Re: Enterprise Guide Locks Excel File

Tom — Wed, 24 May 2023 21:04:48 GMT

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.

Re: Enterprise Guide Locks Excel File

Michael_W — Wed, 24 May 2023 21:13:12 GMT

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?

Re: Enterprise Guide Locks Excel File

Tom — Wed, 24 May 2023 21:46:02 GMT

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 .....

Re: Enterprise Guide Locks Excel File

Michael_W — Wed, 24 May 2023 21:49:41 GMT

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!

Re: Enterprise Guide Locks Excel File

Tom — Wed, 24 May 2023 21:55:24 GMT

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.

Re: Enterprise Guide Locks Excel File

SASKiwi — Wed, 24 May 2023 22:49:52 GMT

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.

Re: Enterprise Guide Locks Excel File

AlanC — Thu, 25 May 2023 04:45:40 GMT

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.

Re: Enterprise Guide Locks Excel File

Kurt_Bremser — Thu, 25 May 2023 04:56:03 GMT

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.

Re: Enterprise Guide Locks Excel File

Michael_W — Thu, 25 May 2023 14:59:02 GMT

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.

Re: Enterprise Guide Locks Excel File

Reeza — Thu, 25 May 2023 15:21:31 GMT

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()?

Re: Enterprise Guide Locks Excel File

Michael_W — Thu, 25 May 2023 15:34:34 GMT

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!

Re: Enterprise Guide Locks Excel File

SASKiwi — Thu, 25 May 2023 21:23:03 GMT

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.

Re: Enterprise Guide Locks Excel File

Michael_W — Thu, 25 May 2023 21:28:34 GMT

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.

Re: Enterprise Guide Locks Excel File

SASKiwi — Thu, 25 May 2023 23:04:16 GMT

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.

Re: Enterprise Guide Locks Excel File

Quentin — Fri, 26 May 2023 00:53:14 GMT

If you change to using libname XLSX do you have the same problem?

I think the Excel file might be locked after the libname statement runs. But then you can use libname libref clear; to explicitly clear the libref, and release the lock.

I suppose it's possible that PROC IMPORT is creating a libref behind the scenes, and somehow the statement to clear the libref is failin on occasion.. Just a guess. I don't use PROC IMPORT much.

Re: Enterprise Guide Locks Excel File

Michael_W — Mon, 08 Jul 2024 18:13:00 GMT

Open a Track is the recommended solution? I did and SAS Tech support wasn't able to identify the root cause or solution. If you took your car to the mechanic and told him that it was only starting sporadically, and his response was "We start cars all of the time. You should take it to a mechanic" .... What would your reaction be? To recap, this issue appears to occur when I run Proc Import from an EG code node and an error is encountered. I know how to resolve the error as I just need to open the Excel file and change the PPI column type to number; however I can't (as you can see) because Windows thinks it's in use. The problem with doing that is that I have to shutdown my EG session entirely just to free up the Excel file. If the error doesn't occur, the file isn't locked.

SYMBOLGEN: Macro variable BENYR resolves to 2024
MPRINT(IMPORTREFTBLS):   PROC IMPORT DATAFILE="G:\Risk Adjustment\Edge Server Processing\Change Control Tables\Revenue and
Enrollment\2024\Enrollment Segs to Exclude.xlsx" OUT=WRKMBR.CCT_Segs2ExclA (WHERE=(PPI EQ 1) RENAME=(IssuerEnrolleeID=SubID_))
DBMS=XLSX REPLACE;
MPRINT(IMPORTREFTBLS):   RXLX;
SYMBOLGEN: Macro variable BENYR resolves to 2024
MPRINT(IMPORTREFTBLS):   SHEET="Excluded Segments_2024" ;
MPRINT(IMPORTREFTBLS):   QUIT;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
      options MSGLEVEL=I.
ERROR: WHERE clause operator requires compatible variables.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Enterprise Guide Locks Excel File

MarkAllemang — Mon, 08 Jul 2024 19:04:53 GMT

@Michael_W - I am a developer on EG and just noticed your reply today. Apologies for not noticing this topic sooner.

A few thoughts:

It would be very helpful to follow the suggestion above to use Process Explorer. The downside there is that it would need to be run on the server machine, and you may not have access to that. But if that can be done by you or an admin, it will definitely say what process is locking the file. I suspect it will be the workspace session (sas.exe).
I have tried to rep without success. You mentioned it is intermittent. Any pattern to the scenario will be helpful as intermittent issues are always much more frustrating to diagnose than repeatable ones are.
If you have access to SAS Studio on that server, it would be interesting to try the same code from there as that takes EG out of the equation. I understand that your overall process that this code node is involved in may prevent running in Studio.

I am very puzzled at the difference in behavior between disconnecting the workspace server in EG and exiting EG. They should have the same effect.

But if I read this thread correctly, stopping and restarting the server session doesn't cause the lock to be released. If the only reference to the xlsx file is in the sas code, then EG is not accessing the file. All access is taking place on the server.

Is the file on your project process flow as a node? If so, that could cause EG to access it.

If this last point is correct, (shutdown releases but disconnect and reconnect doesn't), then I recommend:

Open the track with tech support again.
Provide an EG log file of a session when it happens, and you disconnect/reconnect and the lock persists, and then you exit EG. And then confirm the lock is released.
Ask them to indicate in the track that you interacted with me on communities and I asked for the logs. That will result in me getting a ticket to track the work from an R&D perspective.

That log will help me determine if there is some extra step that is causing the exit to behave differently than the disconnect. I have reviewed the code and do not see anything.

I'm sorry this is proving difficult to resolve - I understand it must be very frustrating.

Thanks,
Mark Allemang

Re: Enterprise Guide Locks Excel File

Tom — Mon, 08 Jul 2024 19:22:07 GMT

Hidden in the middle of that picture you posted as this:

Do you only have trouble with SAS not releasing the lock on the XLSX file when you get that ERROR message about the WHERE= dataset option? (NOTE: Since it is SAS code that is touching the XLSX file the fact that you are using Enterprise Guide to submit the code is probably unrelated to the cause. The symptom you mention of the file getting freed with EG is closed is probably a side effect of EG closing the SAS session it is using to run the SAS code. Next time it locks a file try just telling EG to restart the SAS session and see if it frees the lock.)

What happens if you change your code to NOT use the WHERE= dataset option in the step that is accessing the XLSX file?

proc import .... out=WORK.xxx ....;
data WANT; set work.xxx;
  where PPI=1;
run;

Or change the way the WHERE= condition is coded so that the TYPE of the variable being referenced does not matter?

where=(cats(PPI)='1')

Re: Enterprise Guide Locks Excel File

Michael_W — Mon, 08 Jul 2024 20:08:09 GMT

My Mark,
Thank you so much for your response! I really appreciate. Please allow me to respond in line to your feedback:

It would be very helpful to follow the suggestion above to use Process Explorer. The downside there is that it would need to be run on the server machine, and you may not have access to that. You are correct, I don't have access to that , but our SAS Admin does and I have passed the file and code along to him to replicate the issue.
You mentioned it is intermittent. Any pattern to the scenario will be helpful as intermittent issues are always much more frustrating to diagnose than repeatable ones are. It is intermittent, sort of. Today I was able to replicate the issue pretty consistently. The pattern seems to be that if the error occurs (as shown in the log), the lock is applied and never released by SAS.
If you have access to SAS Studio on that server, it would be interesting to try the same code from there as that takes EG out of the equation. I do and will try that now along with replicating it from a new EG session. The issue is replicated when I create a brand new EG project from scratch AS WELL AS when I run it from SAS Studio. Closing out the Process Flow does not free up the file. However signing out of SAS Studio does (free it up).
I am very puzzled at the difference in behavior between disconnecting the workspace server in EG and exiting EG. My apologies but I don't know what you mean by "disconnecting the workspace server in EG" .. Is that done automatically when the code has completed execution of something that I would do manually?
. If the only reference to the xlsx file is in the sas code, then EG is not accessing the file. I agree and after replicating it in SAS Studio, it doesn't appear to be an EG problem after all.

But if I read this thread correctly, stopping and restarting the server session doesn't cause the lock to be released. I am not sure what you mean by the "server session". I am using EG to submit my code. Once the error occurs, the lock occurs and cannot be released until I close my EG session. To be clear, once I do (close) the lock is released.