Trouble Importing XLS File Into SAS

[JackZ295]

I tried importing an XLS file into SAS (see attached file). However, not all of the columns of the Excel file were read in. Only the first nine columns were read in. Is there a reason why this is happening? It has never happened before. This is the code I used: 

libname trial '/home/username/Redo';

/*Importing Sample Data Set*/ 
proc import out=example
datafile='/home/username/Redo/sample.xls'
dbms=xls replace; 
getnames=yes;
run; 

I replaced my username with "username" for privacy reasons. I know that I can use the "special range" option in the PROC IMPORT statement, but I want to avoid doing that, as that involves me going into the Excel spreadsheet and creating the specific range. I want to be able to import the Excel spreadsheet without having to play around with the spreadsheet. Is there a way around this? 

Thanks for reading! 

[ballardw]

I don't see any reason that only 9 columns would get imported.

Personally with the mishmash of date format you have in there I would be strongly tempted to save the XLS to CSV and import the resulting CSV file. One reason is that when you use a CSV you can add the option GUESSINGROWS=MAX; to examine more rows to set variable properties like informats and lengths. Your data has a moderate amount of oddness that I think that flexibility may be helpful.

Additionally the LOG will have the data step code used to read the CSV that you could copy to the editor and modify to make minor corrections.

[JackZ295]

Thanks @ballardw , I want to avoid converting my Excel file as well if possible. Did you try importing the provided file, and were you able to successfully import all 37 columns? Could there be something about the Excel file that is causing this issue? 

[Patrick]

---

@JackZ295 wrote:

Thanks @ballardw , I want to avoid converting my Excel file as well if possible. Did you try importing the provided file, and were you able to successfully import all 37 columns? Could there be something about the Excel file that is causing this issue? 

---

Most people won't download Excel files.

I can replicate what you describe but didn't find an explanation why this is happening. I also didn't find a way for the existing xls to import all the columns.

I had no issues importing the data when saving the file as XLSX and then use dbms=xlsx in the proc import step.

Also with XLSX you will need some post processing because for example there are cells under the DoB column which are specifically marked as text - and though SAS will read everything into a character variable.

And here what I get when using the EG Import Wizard. I certainly didn't have the file open so...

[ballardw]

No I did not attempt to import the file. It is one thing to look at the read only version that I can look at and a complete other issue to attempt to import a file. Proc import has a lot of issues related to version of SAS (64 or 32 bit) and the Excel file version. So I won't import a file from an unknown source. Especially when a quick glance at the actual contents tells me that Import is almost certainly going to provide some form of garbage because the source file shows evidence of bad data entry practices.

You may be able to read the data with a PCfiles server, but again I am not going to with a file from an unknown source. 

Not to mention there are people that create HTML, XML and other file formats and then name them with XLS to force Excel or other spread sheet software to open the file. But the underlying contents are not actually XLS.

[Tom]

Open a ticket with SAS support.

Can you get the provider to create an XLSX file instead of the old XLS format?  The XLSX format is published and debug-able.  Plus it will reduce the size by over 50%..