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')
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 - 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.
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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.