Enterprise Guide Locks Excel File

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

[MarkAllemang]

@Michael_W  - I understand and you are correct about needing to get the root issue solved. I'm not involved with the implementation of the procedures like PROC IMPORT, so that is why I suggest reopening your track with this new info. They will be able to get the right people involved to find out the root cause. When you open the track, indicate it is in SAS Foundation, instead of starting with the EG support.

Glad to help. Hope they are able to resolve it quickly.

[Michael_W]

Definitely. And thanks for all of the tips and advice.

[AlanC]

Well, you could also consider doing it w/o using PROC IMPORT. ODBC, OleDB are both viable as well.

If the formats, formulas, styles, etc. don't matter, treat Excel as a data source.

[Quentin]

---

@AlanC wrote:

Well, you could also consider doing it w/o using PROC IMPORT. ODBC, OleDB are both viable as well.

If the formats, formulas, styles, etc. don't matter, treat Excel as a data source.

Also as in my suggestion earlier in the thread, libname statement with XLSX engine may work and allows you to manually release the lock by clearing the libref.