Re: Import Excel to SAS

dapenDaniel · Posted 08-08-2018 04:11 PM

Hello,

I am importing some excel files to SAS. I wrote the code first and the code is following below.

PROC IMPORT OUT= WORK.d DATAFILE= "E:\Excel\d.xlsx" 
            DBMS=xlsx;
Run;

However, it works for some files but does not work for other files. If it does not work, there is a popup window as following below.

For those that code does not work, I tried to use wizard to import them but some values that exists in the original file will be missing.

I use Excel 2016 (32-bit).

For SAS, I am using the university edition, version 9.4 (32). Is there any restriction for university edition? Thanks in advance!

PaigeMiller · Posted 08-08-2018 04:19 PM

I'm surprised your code works at all.

DATAFILE= "E:\Excel\d"

is not a name of a valid Excel file. Do you have this problem is you use valid Excel file names?

--
Paige Miller

dapenDaniel · Posted 08-08-2018 04:28 PM

The file name is a combination of letters and numbers such as C01d.

ballardw · Posted 08-08-2018 05:50 PM

Is it always the same XLSX file(s) that cause the problem?

If so, I would examine those files to see if someone has lied to Excel and named a file that is actually a different format with the XLSX extension.

Try opening one of the problem files in NOTEPAD (not Wordpad it may hide the content of certain file types).

If you see something like readable text you likely have the fake name issue. If lots of commas then rename the file with CSV and try to import with syntax for CSV.

If you see something at the top of the file that looks at all like:

<?xml version="1.0" encoding="windows-1252"?>

the file is actually xml. This may be "corrected" by opening in Excel and forcing a SAVE as to xlsx.

if the file starts with something like <html > then the file is actually HTML and the same open in Excel and force a save to xlsx should work. The force might involve making a change such as column width before saving so Excel thinks the file changed.

But better might be saving all of the files as CSV and importing those with guessingrows=max; option in the proc import code.

The import for Excel files only uses a few rows to guess as to type and length of variables and may get things wrong. If you search this forum for "EXCEL Import" you will find many posts, typically 2 or 3 per day, that have some problem arising from using proc import with Excel files.

Kurt_Bremser · Posted 08-09-2018 02:59 AM

The message that sas.exe is not working CANNOT come from SAS University Edition. It is a pure Windows message concerning the Windows SAS executable.

UE runs in a UNIX virtual machine and does not use sas.exe, but an executable called simply sas.

So you seem to run a fully licensed SAS locally on your desktop.

To get around any problems concerning importing Excel data into SAS, always save to csv from Excel and import that with a data step.

All other methods (proc import, libname excel etc) involve guessing and are unreliable. And because of the inherent unreliability of Excel and the Excel file format itself, it is not suited for the transfer of data.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX