BookmarkSubscribeRSS Feed
ysk
Calcite | Level 5 ysk
Calcite | Level 5

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.

2 REPLIES 2
Reeza
Super User

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 2 replies
  • 2459 views
  • 0 likes
  • 3 in conversation