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.

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

[MarkAllemang]

Hi @Michael_W 

In EG, if you right mouse on a connected server, there is a menu item to disconnect. That ends the workspace process on the server.

The same thing can be done in SAS Studio:  

(I think this is in all versions..... I only have the Viya 4 version right now.)

Options -> Reset SAS Session

Your tests so far have eliminated EG and Studio as the sources of the lock. I suspect the other poster here is correct and something about the error condition may be causing the lock to not be released by proc import. If we can confirm that, then it will make it easier to get the tech support ticket routed to the correct development team.

[MarkAllemang]

@Michael_W  - one more thought....

If you open or run proc print on SASHELP.VEXTFL it will show the active filerefs in the workspace. I suspect when you encounter the problem, your excel file will be listed as one. That will further confirm that it isn't getting cleaned up by proc import.

[Michael_W]

Your suspicion is correct. There are no entries in the SASHELP.VEXTFL referencing my file before I run my code, but there are 3 entries right after I run it.
fileref
xpath
xengine
modate
filesize
level
directory
exists
temporary
EGSR
E:\SAS Temporary Files\_TD6712_MP-SAS-A02_\#LN00006
TEMP
08Jul2024 14:08:07
982
0
no
yes
no
#LN00040
G:\Risk Adjustment\Edge Server Processing\Change Control Tables\Revenue and Enrollment\2024\Enrollment Segs to Exclude - Test.xlsx
DISK
08Jul2024 13:01:23
45855
0
no
yes
yes
#LN00041
G:\Risk Adjustment\Edge Server Processing\Change Control Tables\Revenue and Enrollment\2024\Enrollment Segs to Exclude - Test.xlsx
DISK
08Jul2024 13:01:23
45855
0
no
yes
yes

[Tom]

You should definitely check and add the extra FILENAME statement eliminate the fileref when you are done with it.  But I suspect it will not help since I have seen other cases where SAS refuses to close a fileref or libref when it thinks it still has it open, which is seems is what is happening in your case.

Reading from a spreadsheet into data is a risky business because spreadsheets are NOT designed for DATASETs.  They are designed as a scratch pad where you can put any kind of data into any random cell.  A dataset needs to have only one type of data in each variable.

You might want to start a new thread on this forum where you describe what it is you trying to do with the spreadsheet and perhaps someone can provide you with a more more robust method that will not trigger that SAS bug.

Some examples of things you might try:

• As I have said twice in this thread already lose the WHERE= dataset option from that PROC IMPORT step.
• The particular WHERE clause in your latest image is one that is easy to rewrite into form that does not depend on a particular variable being numeric or character.  But it would still fail if the variable was not there at all.
• You can use GETNAMES=NO to treat the first row as part of the data.  In which case IF the first row has normal variable names then that should force PROC IMPORT to make every variable character.  Then you can be more confident when writing the SAS code about the variable types.