|Date: ||Thu, 2 Sep 1999 08:11:53 -0400|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|Subject: ||Re: Importing Excel files into SAS|
Yes, but did they tell you that if the extension is not xls, then PROC ACCESS
A silly restriction, no?
From: Hockey Richard <email@example.com> on 09/02/99 01:59 AM
Please respond to Hockey Richard <firstname.lastname@example.org>
cc: (bcc: Michael Bramley-M/PGI)
Subject: Importing Excel files into SAS
I just found atech note on the SAS site which solves some of the problems I
have had importing Excel data into SAS, particularly when I have mixed data
in a field.
It is at :
There is also an addon to import excel97 files but I wouldn't recommend it.
It is sooo sloww!!!
Here is the contents of the tech note:
SAS/ACCESS reads date fields in as character if first created as text
If you are reading in an Excel spreadsheet that contains date fields which
were originally created as text fields, SAS/ACCESS to
PC File Formats will read the field in as character.
If you create an excel text field that contains dates, then you use
FORMAT-->CELLS to change the field to a date (mmddyy) field, excel
does not change the internal storage of the data. It is still
being stored as character (text) data, but is being displayed as a
date type. Excel is failing to convert the value to a date (numeric).
Therefore when the field it read into SAS it becomes a character '
To circumvent this problem you must force Excel to change the storage
of the field by doing the following within Excel:
Choose DATA --> Text to Columns
Select Fixed Width, then click NEXT button 2 times
In upper right corner of window select Date as the Column Data Format
This will actually change the field to a date field, thus allowing SAS
to read it in as expected.
Richard Hockey email: mailto:email@example.com
Senior Data Analyst Phone:+61 7 3840 1587
Queensland Council on Obstetric Fax:+61 7 3840 1588
and Paediatric Mortality and Morbidity
South Brisbane , QLD 4101
Australia URL: http://www.uq.net.au/qcopmm/
<<Richard L Hockey.vcf>>
Richard L Hockey.vcf [application/octet-stream]