topic Re: Import of Excel sheet crashes SAS. in SAS Programming

Import of Excel sheet crashes SAS.

WouterKBC — Fri, 26 Aug 2016 13:55:02 GMT

SAS crashes when trying to import a specific Excel sheet.

I never had any problems with importing Excel sheets in SAS until now. I use the exact same method as I always use. I can import other sheets from the same file.

But with this specific sheet, SAS crashes every time. When I try to run it on a remote server, this server disconnects.

The problem is sometimes resolved when removing some columns from the sheet. When I get it to work and slowly reintroduce the other columns, I can run it without any problems. When I run the exact code on an exact copy with the exact same columns and data, it doesn't work anymore, unless I apply the same procedure that I described above again.

This doesn't seem to be a data problem, because it doesn't happen if the fields get reintroduced slowly. I feel like SAS cannot handle too much new fields at once. (I'm talking about 70 fields that will be imported.)

The problem also occurs when there is only 1 position in the Excel sheet.

I've also tried to remove all layout, which doesn't help either.

This sheet is an outputfile of another procedure which runs automatically, so I cannot/may not alter this data.

The code that I use for importing the Excel sheet:

PROC IMPORT OUT=&datalib..src_&dataset.
DBMS = xlsx
datafile = "&inputfile";
SHEET = "&sheet";
getnames = yes;
RUN;

Re: Import of Excel sheet crashes SAS.

Reeza — Fri, 26 Aug 2016 14:03:28 GMT

Sounds like a SAS tech support question.

Re: Import of Excel sheet crashes SAS.

RW9 — Fri, 26 Aug 2016 14:10:11 GMT

Excel has many hidden elements which can affect things. Why can you not save the Excel file to CSV and import that - that would generally be my preferred method anyways. 70 columns shouldn't be a problem from a technical standpoint - although programming with that many columns would be a nightmare.

One thing you could try is to highlight the range in Excel and copy->paste to a new empty sheet, paste only values. If that imports you can tell something is hidden.

When you say this sheet is an output of another procedure, what do you mean, is it created from SAS?

Re: Import of Excel sheet crashes SAS.

WouterKBC — Fri, 26 Aug 2016 15:00:12 GMT

The problem has been solved.

When I saved my sheet as csv, I noticed that 73 fields were imported while there should only be 70. When I checked the csv, I noticed that there were 3 ';' too much. Those fields were empty.

So I went back to the Excel sheet and deleted the 5 following columns (to be sure) and it works!

At this moment I don't understand why this is. The columns seem completely empty but at least I have a workaround.

Thanks for all the help!

Edit: It might be a good idea to print an error message so the user has a clue what might cause the problem.

Re: Import of Excel sheet crashes SAS.

TomKari — Sat, 27 Aug 2016 01:08:26 GMT

Weird! I would just sent this along to Tech Support. SAS isn't acting correctly...it shouldn't crash when this happens.

They may be interested in finding out more about your environment, to figure out why it's happening.

Tom

Re: Import of Excel sheet crashes SAS.

WouterKBC — Thu, 15 Sep 2016 15:15:56 GMT

The fact that the previous workaround worked was probably coincidence, because it doesn't anymore at the moment 🙂

I was completely wrong. It has nothing to do with the amount of lines or the last columns. I have received new files (with a low number of lines) which have the same problem. The solution is very simple: remove all formatting. That's it.

For some reason SAS cannot handle the formatting that is being used in the excel sheets...