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.
@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.
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:
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.
@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.
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 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.
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
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?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: