Enterprise Guide Locks Excel File

[Quentin]

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.

[MarkAllemang]

@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

[Michael_W]

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.

[Tom]

I don't use EG but it does have the same functionality as SAS/Studio where you can re-start the SAS session (or switch to a different SAS server for that matter) without having stop EG.

In SAS/Studio it is located in the menu that is just to the left of the ? icon.

[Michael_W]

Ah, I see, Yes, I can disconnect from and then reconnect to the SAS server and that frees up my file. My concern is that if I do that, doesn't that clear our any global macro variables or WORK tables. If it doesn't, then that will definitely work as a suitable workaround. If not, it will save a little time not having to save my SG session, exit and restart ...but the bulk of the time will be re-establishing all of the macro variables, librefs and work data sets.

[MarkAllemang]

Yes, that completely resets your session, so environment variables, librefs,etc are all reset. So it likely is not a good workaround for you.

[Michael_W]

Responding to both of your recent posts ...
Yes, the disconnect option released the file but is not a suitable workaround. Sure I could update the code to fix the problem (which I did), but it doesn't explain why SAS is locking the file when an error of this type is encountered. SAS should be able recover from an error that it has encountered and release the file, IMO.

[Quentin]

---

@Michael_W wrote:
Responding to both of your recent posts ...
Yes, the disconnect option released the file but is not a suitable workaround. Sure I could update the code to fix the problem (which I did), but it doesn't explain why SAS is locking the file when an error of this type is encountered. SAS should be able recover from an error that it has encountered and release the file, IMO.

---

Agree, a graceful exit is always to be preferred.

Just wondering, your initial thread did not mention getting an error during PROC IMPORT.  In your further explorations, did you find this locking problem only happens when your receive an ERROR from your WHERE statement?  If so, it might be helpful to provide that information to tech support, to see if they will agree to open the issue as a bug.  It seems much better defined if the problem is reproducible.  It's certainly plausible that the error is causing SAS to exit the PROC without releasing a lock, and that would feel like a bug to me.

[Michael_W]

Hi,
I included the log which had the error message from the very start. To repeat, the error is not the problem, it's SAS unwillingness to release the file after the error is encountered. In your further explorations, did you find this locking problem only happens when your receive an ERROR from your WHERE statement? Well no. The WHERE clause is failing cause the error to occur and thereby locking up the file. I can't reach tech support other than to call them as their portal is unavailable (maybe blocked by us who knows).

[Quentin]

---

@Michael_W wrote:
Hi,
I included the log which had the error message from the very start. To repeat, the error is not the problem, it's SAS unwillingness to release the file after the error is encountered. In your further explorations, did you find this locking problem only happens when your receive an ERROR from your WHERE statement? Well no. The WHERE clause is failing cause the error to occur and thereby locking up the file. I can't reach tech support other than to call them as their portal is unavailable (maybe blocked by us who knows).

---

I was referring to you original message from the start of this thread (from 2023); it did not have an error message.  It said that the PROC IMPORT ran successfully.

But now it sounds like when the WHERE clause causes an ERROR, SAS is not releasing the file lock (which sounds like a bug).

I was not clear how that bug relates to your initial report of this problem, from 2023. It's interesting that in your initial report, you say it ran successfully, but the output dataset had 0 obs.  So maybe you missed an error message in the intial report, or maybe there is a related bug where SAS doesn't release the lock when you PROC IMPORT an Excel file with 0 obs.

[Michael_W]

Hi Quentin,
I think the disconnect is due to timing of my posts. I originally posted my issue on 5/25/2024 and I most likely didn't include the log as you stated. However, just yesterday I added to it (with the same problem) but different information. I just scanned through this track and I cannot find my post that include the log and a screenshot (no idea where it went to, but I have provided below). Sorry for the confusion.
24 ! PROC IMPORT DATAFILE="G:\Risk Adjustment\Edge Server Processing\Change Control Tables\Revenue and
24 ! Enrollment\2024\Enrollment Segs to Exclude - Test.xlsx"
25 OUT=CCT_Segs2ExclA (WHERE=(PPI EQ 1) RENAME=(IssuerEnrolleeID=SubID_)) DBMS=XLSX REPLACE;
26 SHEET="Excluded Segments_2024" ;
27 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

[Tom]

Can you also post the log you get when you fix the PROC IMPORT code to prevent the invalid expression issue?

PROC IMPORT
 DATAFILE="G:\Risk Adjustment\Edge Server Processing\Change Control ables\Revenue and Enrollment\2024\Enrollment Segs to Exclude - Test.xlsx"
 OUT=CCT_Segs2ExclA (WHERE=(cats(PPI) EQ '1') RENAME=(IssuerEnrolleeID=SubID_)) 
 DBMS=XLSX REPLACE;
 SHEET="Excluded Segments_2024" 
;
RUN;

Does that code generate the data you want?

Does it end up with the XLSX file being locked?

[Michael_W]

24 ! PROC IMPORT DATAFILE="G:\Risk Adjustment\Edge Server Processing\Change Control Tables\Revenue and
24 ! Enrollment\2024\Enrollment Segs to Exclude - Test.xlsx"
25 OUT=CCT_Segs2ExclA (WHERE=(PPI EQ '1') RENAME=(IssuerEnrolleeID=SubID_)) DBMS=XLSX REPLACE;
26 SHEET="Excluded Segments_2024" ;
27 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.
NOTE: The import data set has 1 observations and 9 variables.
NOTE: WORK.CCT_SEGS2EXCLA data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
Does that code generate the data you want? YES
Does it end up with the XLSX file being locked? YES

[Tom]

You should report that to SAS also, because that seems at odds with your earlier statement that it is only locking the file when the PROC IMPORT step reported an ERROR.

[MarkAllemang]

@Michael_W 
Based on what you have learned, I recommend opening the track again with tech support with this new information. I suspect there is a problem in PROC IMPORT that results in the file access not being closed in the scenario you are hitting. Tech support should be able to route that then.

Key points for the track are that the problem happens in both EG and SAS Studio, and ending the workspace session causes the lock to release.