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]

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

[Tom]

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')

[AlanC]

Tom suggested, in an earlier post, to release the filename. I didn't see that after cursory review. Have you tried it?

filename myFile 'blah, blah' ;

proc import...;

filename myFile; <== Do you have this in place?

[Michael_W]

Yes, it's only SAS and I agree that it's unrelated to EG ..now that I just discovered that I can replicate the issue using SAS Studio. If I remove the WHERE the code will work as it assign the type based on value in that column on the Excel spreadsheet, but that is not what I need my code to do. The first row of the Excel file is used to establish the field/column types and then the same spreadsheet is used by process in two different places; one where the PPI EQ 1 and the other where the PP1 EQ 2. The fix to the problem is easy once the file is unlocked; either update the WHERE to PPI EQ '1' or remove the quote preceding the PPI value on the first row of the worksheet. Resolving my issue in that manner, while it fixes my issue, it doesn't explain or address the problem when it reoccurs (which is SAS locks PC files when this type of error occurs upon import attempt).
I do appreciate your assistance and I am not trying to be challenging, I am just trying to figure why SAS is doing this .. because this type of use case will happen again and I will have to shut EG or SAS Studio completely just to free up the file.