topic Re: Importing Excel TIME format changes in SAS Enterprise Guide

Importing Excel TIME format changes

deleted_user — Wed, 01 Oct 2008 20:11:20 GMT

Hi, I'm new to SAS EG. (no programming background). when I import this excel file into sas (File > Import data > Local Computer >reports.xls).
it gives me this pop up
Import Data with 3 options, Region to Import, Column Options & Results. I click RUN
the reports excel file looks like this
IN OUT
3:56 PM 4:56 PM
6:18 PM 6:48 PM
11:09 AM 11:25 AM

SAS converts it into
30DEC1899:03:56:00 PM 30DEC1899:04:56:00 PM
30DEC1899:06:18:00 PM 30DEC1899:06:48:00 PM
30DEC1899:11:09:00 AM 30DEC1899:11:25:00 AM

WHERE IS IT GETTING 30DEC1899 FROM ???
Please help.
Also, is there a INFILE code to import this file into SAS EG. I have this file saved on my desktop (reports.xls).
Jay

Re: Importing Excel TIME format changes

Doc_Duke — Wed, 01 Oct 2008 21:26:02 GMT

SAS imports Excel times as date-times; I think because Excel stores it as a date-time "under the hood". So it takes the Excel file and does the best it can. If you had formated the Excel column as a Date-Time, it would have shown the first as 1/0/00 3:56 PM and that is what SAS is trying to convert into a **valid** datetime value in SAS.

Time arithmetic still works, so OUT-IN would result in the number of seconds between the two times. (Warning-- it can get messy when the time wraps around to the next day).

You can convert the date time to a time by

IN -('30Dec1988'd * (24*60*60) )

and change the format.

If you have a separate date field (say DateIn), you can convert the IN variable in SAS to a valid SAS datetime by

IN + (DateIn -'30Dec1899'D)*(24*60*60)

(read the SAS Base manual about Dates & Times to understand what I did.).

Without exporting the Excel spreadsheet as a .csv file, I don't know how to use INFILE to get the data in.

Doc Muhlbaier
Duke

Re: Importing Excel TIME format changes

LinusH — Thu, 02 Oct 2008 07:03:25 GMT

Have you tried the SCANTIME=YES option (valid both in proc import and libname excel)? Make sure that your Excel file does not have any date or datetime values in your time columns.

/Linus

Re: Importing Excel TIME format changes

deleted_user — Wed, 23 Dec 2009 10:22:20 GMT

When you get pop up Import Data with 3 options go to Column Options. Find trouble field. And at right you seen window, where you may find "Display format". Choose format that you nedeed, and enjoy )