Enterprise Guide Locks Excel File

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

[Michael_W]

What? "Reading from a spreadsheet into data is a risky business because spreadsheets are NOT designed for DATASETs?" Do you realize how crazy that sounds? Why would SAS have PROCEDURE (i.e. PROC IMPORT) with examples everywhere in their documentation on how to use it import Excel worksheets or ranges and it not be designed for a SAS dataset. I have been using SAS for 15 years to successfully import Excel data into SAS dataset (and so has everyone who uses SAS in my company) and you're telling me that because you can't explain why SAS is not releasing my file that spreadsheets are not designed for datasets? I think it would be better if you just didn't respond at all. I stopped reading you reply after that.

[Tom]

You should be happy.  You have successfully come up with an explanation of the underlying issue in a problem that has been causing your trouble. 

PROC IMPORT has a bug that can cause it to not release an XSLX file when the procedure finds errors.  Such as the using a WHERE= clause in the OUT= dataset that requires a different type of variable than what was found in the XLSX file.

Note that the process for SAS from getting a description of the bug to the final solution might be a long one.  They need to figure what is wrong in their code.  Decide if they can fix it.  Decide if it is worth fixing.  If they decide to fix it they will need to execute the fix and test it (don't want to introduce new bugs!).  And finally schedule it release to the public.

So it is definitely going to be worth your time to examine your business problem and find a solution that avoids the bug.

[Quentin]

---

@Michael_W wrote:
What? "Reading from a spreadsheet into data is a risky business because spreadsheets are NOT designed for DATASETs?" Do you realize how crazy that sounds? Why would SAS have PROCEDURE (i.e. PROC IMPORT) with examples everywhere in their documentation on how to use it import Excel worksheets or ranges and it not be designed for a SAS dataset. I have been using SAS for 15 years to successfully import Excel data into SAS dataset (and so has everyone who uses SAS in my company) and you're telling me that because you can't explain why SAS is not releasing my file that spreadsheets are not designed for datasets? I think it would be better if you just didn't respond at all. I stopped reading you reply after that.

---

Easy, the general statement that Excel sheets are a poor way to store data is commonly accepted wisdom here, shared among many folks who have many more than 15 years of SAS experience.  : )

That said, of course Excel has (unfortunately) become the lingua franca for data storage in many businesses.  So when confronted with Excel data, as we all often are, the choice is whether to import it from Excel (using one of the many different excel engines provided in SAS, each of which has its own limitations), or export the data to, say CSV, and then import from CSV.  

Personally, for short-term ad hoc stuff I typically roll my eyes, groan, and import from Excel.  But that means I also deal with the fact that every couple weeks, someone enters 'N/A' into an Excel DATE column, and because EXCEL doesn't really have columns, just a collection of cells, a column that had been imported as a date is now imported as a character variable and all my code breaks.  So then I have to decide whether to change my code to import the values a character value and manually convert them back to SAS dates, or I need to ask the users to stop writing 'N/A'.

Or last week I had the opposite problem.  My dates had been coming in as character, which was fine, and I had converted the date columns to be numeric.  Then one day the date columns started being imported as dates.  It turned out there was a row at the bottom of the spreadsheet that said "End of Data" in every cell, which had forced all the columns to be character.  One day someone decided to delete hat row.  Sigh.

So no, you're not crazy to use the XLSX engine to import data from Excel.  But Tom is also not crazy to mention that there are risks that come with storing data in Excel, that don't come with other storage formats.

That said, as I see from Tom's follow-up post, I think we all agree that it sounds like you've found an interesting bug in PROC IMPORT.  So yes, it's fair for you to be annoyed by this bugs.  Bugs are annoying.

[Michael_W]

If you're claiming that importing from Excel may carry some risk and may require some coding accommodations, then I certainly agree with that.

[AlanC]

SAS has never worked with Excel very well. Excel may be the largest storage format, for numbers, in the world. I do not use SAS with Excel directly but use other tech to work with Excel and feed it to SAS. I love Excel as do millions of others.

As a Microsoft/SAS consultant, almost every engagement has involved Excel. If you are encountering issues with SAS, and want to solve it in SAS, I suggest using the libname (as Quentin stated), or OleDB to read data.

Excel is not a good datasource but people love it and it is a standard. Excel was not created to be what it has become but so what? Can SAS read the cell color, italics, spacing, and other weird ways people layer metadata onto values? I dont think so (correct me if I am wrong). Hence, I usually go around the SAS/Excel dilemma and have each do what they are best at. I use intermediate tech to handle the data back and forth.   If you don't face these kind of dilemmas, use Quentin's suggestion or a db tech.

[Michael_W]

Interesting. I would never have attempted to import from Excel if SAS online documentation didn't explicitly mentioned XLSX as a DBMS option, but today there is no mention of it. Given that I have literally 100s of SAS jobs that import Excel files and that have been running successfully for years, my options are to change all them as you and Quentin have suggested or live with SAS locking them whenever ever an error occurs. I am think I am going to go with the latter.

[Tom]

---

@Michael_W wrote:
Interesting. I would never have attempted to import from Excel if SAS online documentation didn't explicitly mentioned XLSX as a DBMS option, but today there is no mention of it. Given that I have literally 100s of SAS jobs that import Excel files and that have been running successfully for years, my options are to change all them as you and Quentin have suggested or live with SAS locking them whenever ever an error occurs. I am think I am going to go with the latter.

---

Since the issue seems to be intermittent (in the example you have shared it will only happen when the PPI column in the particular spreadsheet being read is character instead of the numeric variable that the code is expecting) I would suggest a more gradual approach. 

When you find that one has locked a file then at that point take the time to investigate the root cause.  And if possible then modify that particular code to prevent the locking.

[Michael_W]

I agree with your more gradual approach recommendation. However, I wouldn't characterize the error caused by the WHERE clause as the "root cause". Sure that is what triggered the error and possibly caused the file to get locked by SAS, but it's not what is causing SAS not to release the lock after the error is encountered. But you're right, if I never encounter an error again when importing an Excel file, I should be ok.

[AlanC]

I never suggested refactoring your whole code base. If you have code that works, stick with it.

As a Microsoft/SAS consultant, I am just telling you what I encounter in the field. A company has nowhere near the variation as what I see across dozens of companies so YMMV. In this case, switching it to a DBMS source may eliminate the object type issue. As Quentin said, "N/A" breaks a numeric column. It does in SAS, under some access methods, but I can get around it using different tech. SAS does not have a concept of a try/catch block and SAS is a loosely typed language. Excel cells are an object, not a value. SAS has to treat it as a value but a cell is a container with properties, methods, etc. Same with rows and columns. 

Excel is best read with Excel or Office type products. Whether it is Office interop or 3rd party libraries such as GemBox or Aspose, there are much faster, better ways to read Excel. SAS works and I have used it a lot. However, if you have to create 10K worksheets a day, SAS cannot do it (yes, that is a real volume from a real SAS client). Interop can't do it effectively either. If you have to read colors or bold/italic, it is hard w/o the metadata access.

If it is a SAS bug (looks like it) then they can fix it. In the meantime you work around it or wait for a patch. I would choose the former but your call. 

[Michael_W]

Hi Alan,
I think I understand and agree with your position, however I am not sure that we are aligned on what the problem actually is. I don't and never expect SAS to miraculously work universally or with bad data or poorly crafted code. What I expect (and perhaps this bears repeating) is that SAS is capable of releasing a PC file after an error is encountered/detected by SAS. That is it. Nothing more. It shouldn't take disconnecting from the SAS server or restarting SAS Studio or EG to free the file up.
If you're wondering (and I doubt you are) why I am so hung on his issue, well it's three-fold:


1. I can't prevent the Excel files that I import from always being 100% compatible with the PROC IMPORT WHERE clause. I can certainly try (and will), but there is no guarantee it won't happen.
2. Similar to #1, I can't always ensure that the PROC IMPORT WHERE clause will be aligned the filtered column type. The vast majority of the time, they are (aligned).
3. Most of my EG projects are designed to set up the environment macro variables, libraries, etc. based on a user prompt response and other system variables. Each time an external file is lock and I have to disconnect or restart and when I do I lose all of the environment settings; which takes time to reset and present additional opportunity for entry error.
No response is required and I very much appreciate you take time out for your day(s) to help me. At this point, I am not looking for a solution cause it sounds like one isn't available (aside from not getting the SAS error) and we can officially close this track.

[Michael_W]

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