BookmarkSubscribeRSS Feed
WouterKBC
Obsidian | Level 7

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;

5 REPLIES 5
Reeza
Super User

Sounds like a SAS tech support question. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

WouterKBC
Obsidian | Level 7

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.

TomKari
Onyx | Level 15

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

WouterKBC
Obsidian | Level 7

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...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3230 views
  • 0 likes
  • 4 in conversation