SAS crashes when read excel files using EXCEL engine

[YTang]

I recently encountered a weird SAS problem. My base SAS crashes on the 3rd excel file after imports two excel files using the EXCEL engine. This happens when I use libname statement EXCEL engine to read excel files or when I use proc import dbms=excel to import excel files. Again, using the EXCEL engine, I could successfully read or import up to two excel files, but SAS crashes on the 3rd excel file to be imported. If I switch to XLSX engine, there is no issue. Can anyone help me to resolve the issue with EXCEL engine?

My SAS version: SAS 9.4 TS Level 1M5 X64_10PRO platform

Operating System information: Windows 10

The analytical products: 

SAS/STAT 14.3

SAS/ETS 13.1

SAS/IML 14.3

Here is the code that cause the problem when I try to read the EXCEL files:

LIBNAME Source excel "File_Name.xlsx" MIXED= YES DBMAX_TEXT= 32767 HEADER= NO;
PROC SQL;
CREATE TABLE Output_Dataset AS
SELECT F1 AS COL1 LABEL= "Page" format=$32.,
INPUT(F2, 8.) AS COL2 LABEL= "Mark1",
INPUT(F3, 8.) AS COL3 LABEL= "Mark2",
INPUT(F4, 8.) AS COL4 LABEL= "Mark3",
INPUT(F5, 8.) AS COL5 LABEL= "Mark4"
FROM Source."Sheet_Name$"n
(firstobs = 6 obs = 6)
;
QUIT;
LIBNAME Source CLEAR;

PROC IMPORT datafile="File_Name.xlsx"
OUT=Output_Dataset
DBMS=EXCEL REPLACE ;
RANGE="Sheet_Name$";
GETNAMES=YES;
MIXED=YES;
RUN;

[ballardw]

---

@YTang wrote:

I recently encountered a weird SAS problem. My base SAS crashes on the 3rd excel file after imports two excel files using the EXCEL engine. This happens when I use libname statement EXCEL engine to read excel files or when I use proc import dbms=excel to import excel files. Again, using the EXCEL engine, I could successfully read or import up to two excel files, but SAS crashes on the 3rd excel file to be imported. If I switch to XLSX engine, there is no issue. Can anyone help me to resolve the issue with EXCEL engine?

When you have an issue like this, success for two files and failure and especially anything resembling a "crash" on another then the issue is almost certainly the File content.

One thing to be aware of, there are people /applications that name files with an "xlsx" extension to force Excel to be the default program to open the file but the file is NOT actually a "xlsx" file but something else such as HTML or XML or even tab delimited text.

You might try opening that file in Excel, do a file- save as to a new xlsx file and then try using that new file. If that works then the issue is the original file format.

Another issue though it usually leaves lots of notes in the log is related to file encoding, which is a fancy way for saying the language character set used in the file.

You should probably describe a bit more detail of just what "crashes" means as well. Does SAS stop working? Or is the log full of error messages?

[YTang]

Thanks for your quick reply.

This is not the file format or file content issue. I created a very straightforward .xlsx file and imported it into SAS three times. The file was successfully imported the first time and the second time, but SAS crashed when I tried to import the file the 3rd time in the same SAS session. When I say "crash", I mean the SAS interface disappears with no error messages.

[ballardw]

---

@YTang wrote:

Thanks for your quick reply.

This is not the file format or file content issue. I created a very straightforward .xlsx file and imported it into SAS three times. The file was successfully imported the first time and the second time, but SAS crashed when I tried to import the file the 3rd time in the same SAS session. When I say "crash", I mean the SAS interface disappears with no error messages.

---

You missed the detail that you were importing the same file.

Now details. Are you always reading the same file with LIBNAME and then Importing and that worked twice and then failed the third time. Or was the import different attempt than the Proc SQL step?

There may be a bit of time needed for SAS to release the lock on the lock that the LIBNAME creates before the Import starts, especially if the file is on any sort of network drive.

[YTang]

Again thanks for your input.

I am not always using the libname statement to import the same files multiple times. This is just for troubleshooting since you are concerned about file content and file format. I did try importing three different but regular (no formatting issue) .xlsx files, SAS import the first two with no problem, but crashes on the third file. Actually, the code that causes a problem was used many times previously in the same computer with no problem. The issue just came out most recently. We just renewed our SAS license recently. I don't know if there is anything to do with that.   

For your question on "was the import different attempt than the Proc SQL step?", yes, proc import is a different attempt than proc sql. If I use libname statement Excel engine and proc sql to read .xlsx files, SAS crashes on the 3rd file to read in. Now I start a new SAS session and use proc import with dbms=EXCEL to import .xlsx files, SAS crashes on the 3rd file again. What I found is that as long as I use EXCEL engine to read (libname statement with Excel engine followed by proc sql) or import (proc import with dbms=EXCEL) .xlsx files (regular excel files with no formatting issues), SAS failed on the 3rd file to input. Let me know if you need more clarifications. Thanks

[SASKiwi]

If it used to work and now it doesn't then something must have changed to cause it. Are you running SAS on your PC or on a remote server? If it is on your PC, do you have access to another PC with SAS to test your problem? If the problem only occurs on your PC then it is not a general SAS problem.

[YTang]

Thanks for the reply. I am running SAS on my own PC. The same piece of code does work on another PC. My guess is windows update possibly misconfigured something. Do you have any suggestions? Do you think to uninstall and reinstall SAS will help?

Thanks,

[SASKiwi]

OK, so it is a PC-specific problem. I'd be inclined to reinstall MS Office since it is Excel where the problem happens.

[lamesis]

Have you solved yet? I'm facing the same problem, running the same proc import three times crashes the SAS. 

[Kurt_Bremser]

I suggest you switch to the XLSX engine.

[GFW]

Did you have any progress. Mine is four times import leads to crash.😂

[Sajid01]

Looks like you are running SAS on a Windows 10 Desktop.

If I am in this type of scenario I will make sure that the Operating , Microsoft Office and SAS are all  64 bit.

Then the next step would be to check the available resources.(After the second import / reading second file) and before the third import /reading check for available resources (Task Manager/Performance). One should have free memory approximately three time the file to be imported.
If the memory is less, I will check/adjust  adjust the Virtual memory. This can be done by opening All Setting and typing "Advanced system setting " in the search bar. This will open "system properties" window.
Select the advanced. In the new window that opens there at the bottom there is virtual memory.
Adjust as needed. (I have attached a screen shot). 
In company Laptop/Desktop, the user may not have necessary access. You may need the help of Desktop / IT support teams.

Screen capture of Virtual memory Window

[GFW]

Thanks for your reply and detailed solution.

I tried to reinstall the ODBC drivers (AccessDatabaseEngine_X64.exe) and the problem seems to be resolved.

[YTang]

Unfortunately we could not resolve the problem by uninstall and reinstall Microsoft software and SAS. I ended up with getting a replacement computer which has no such SAS issue.  

[GFW]

I tried to reinstall the ODBC drivers (AccessDatabaseEngine_X64.exe) and the problem seems to be resolved.

Since you have reinstalled Office, my solution might not work for you.