topic Re: Importing Excel Files (64bit) in SAS Programming

Importing Excel Files (64bit)

ysk — Mon, 14 Aug 2017 21:13:28 GMT

I recently upgraded 32bit to 64bit SAS (and Excel remained at 32bit).

Historically i had been using proc import to import xls file (such as below)

PROC IMPORT DATAFILE="test.xls"
OUT=DATASET
DBMS=EXCEL REPLACE;
SHEET="SHEET";
GETNAMES=YES;
USEDATE=NO;
MIXED=YES;
RUN;

i was able to run this no problem. However after i upgraded to 64bit SAS i am no longer able to use this.

Only way i can import xls file is below.. But this doesn't allow me to use extra options like getnames usedate..

proc import out=DATASET
datafile='test.xls'
dbms=xls replace;
sheet='SHEET1';
run;

Is there a workaround to force SAS to those options? When i import xls file, the date fields show up as 38443 to 42826.. in the original excel file they show up as 4/1/2016 12:01:00 AM..

Anyone have solution for this? This is very frustrating - i thought it'd be easier than this but very disappointed.

Re: Importing Excel Files (64bit)

Reeza — Mon, 14 Aug 2017 21:31:56 GMT

If you're disappointed because you have to ask a question, coding is going to be an interesting endeavor for you.

Have you tried the DBMS=XLSX option with PROC IMPORT?

Re: Importing Excel Files (64bit)

RW9 — Tue, 15 Aug 2017 08:04:13 GMT

It just highlights the issues with propreitary file formats. XLS is Microsofts old binary Excel file format - it is proprietary and totally unusable without license and knowledge of the file format. Even M have acknowledged that open formats are better, hence have moved onto Open Office format XLSX which is actually a zip file containing XML files and directories. Also bear in mind that your Office system needs to have the correct bitness for operation, a blog on it can be found here;

http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/

My top suggestion for any data transfer, use plain text files such as Comma Seperated Variable files or XML. This is totally portable across systems, can be read by any application, and with a properly written datastep import program reads in 100% correct without guessing (proc import is a guessing procedure which tries to guess your data).

A secondary suggestion would be to sav the file as XLSX so at least then you are using the latest version of Excel, but even then cross portability, future updates and such may have the same impact on these files, and using proc import on it will still be guesssing what the data should look like.