SAS Completely Crashes When Doing PROC IMPORT With an Excel File

[Keil]

When I try to read in an Excel file with PROC IMPORT, and also just using the Import Wizard in SAS - SAS completely crashes and closes down. It is not the Excel file - I have tried multiple Excel files, and SAS crashes every time.

Since SAS completely crashes, the only errors I can get are from the Windows Application Log. It throws 2 Windows errors every time. I am attaching screen shots of both, for full details. Thank you for any help with this - I am unable to do my tasks at work because of this issue, and have already spent a day and a half looking for solutions.

[ballardw]

This sounds like a question for Tech support. It sounds more like a SAS install or configuration issue, possibly of SAS or possibly .NET or other software.

Are these files on your local harddrive, on a cloud service (One Drive), network share or somewhere else? Network related issues may be fun to diagnose.

While waiting for a response from SAS Tech support I would suggest 1) save the Excel to CSV format using the File-Save as menu in Excel or your spreadsheet software. Then try importing that CSV file. That should not be hitting .NET at all.

[ChrisHemedinger]

Can you share what code you're using? Which import method? DBMS=EXCEL or EXCELCS? 

I recommend trying with DBMS=XLSX (I'm not sure that the Import Data wizard generates that by default). It directly reads the file instead of relying on Microsoft data access components.

[AMSAS]

I recommend you open a TS track for this question.

Please include the following:
- SAS Version you are running (the following code will report SAS version information)
%put &sysvlong ;
%put &sysscpl ;
%put &syssite ;
- Registry Report (*.txt files) for each server, please clearly name the report files to reflect the server it was executed on.
- Detailed description of the issue you are encountering

- Has this worked in the past?

- When was it last working?

- What has changed since the last time it was working?
- Any supporting log files containing errors

[Keil]

Thank you to all 3 of you for the quick replies! The crash is occurring both when I try to read Excel files from a network drive, and from my C: drive. 

I did not try the csv, but I did try DBMS=XLSX instead of DBMS=Excel, and XLSX worked. 

I will open a tech support ticket. I don't want to have to go rip out code in many of our production programs, especially since several of us on my team use those programs, not just me. And I don't necessarily want to replace code that worked fine for years, and suddenly stopped working on my pc. I have pasted my original code, that is crashing SAS, below.

At least I know I can use xlsx if I can't get anything else working.

PROC IMPORT OUT= WEBCASEXLS_202122
DATAFILE= "L:\BOUNCE\Data\CASEALL_Download_202122.xlsx"
DBMS= EXCEL REPLACE;
RANGE="CASEALL$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

[SASKiwi]

Has your Excel been upgraded recently by any chance? For example to Excel 365? The EXCEL SAS engine relies on MS software to work, while the XLSX engine is provided by SAS. That might explain the behaviour you are seeing.

Are you using SAS on PCs or a remote SAS server? 

[Keil]

No Office upgrade in a long time. But I think you are right - this might be something to do with the underlying utility that SAS uses to communicate with Office files. Maybe somehow it is pointing at the wrong thing now, and crashing. I have no idea what the underlying plumbing is that tells SAS what to use when doing proc import with Excel.

The SAS 9.4 version I am using runs on my work PC

[Keil]

Hello - I just wanted to add details on how I ended up fixing this. Thanks again to everyone who replied. 

I don't know if I would recommend this as the best way to fix this, but it is what ended up fixing the issue for me.

One of the things I saw to try was running the built-in Repair utility on Microsoft Office. But when I ran the repair, it ended up completely uninstalling Office from my PC. So I ended up having to reinstall Microsoft Office on my PC - and after that, PROC IMPORT with Excel files worked again. 

[SASKiwi]

@Keil - Please update your post as answered in that case.