Desktop productivity for business analysts and programmers

Importing Excel TIME format changes

Posts: 0

Importing Excel TIME format changes

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
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

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).
Valued Guide
Posts: 2,111

Re: Importing Excel TIME format changes

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
Esteemed Advisor
Posts: 5,202

Re: Importing Excel TIME format changes

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.

Data never sleeps
Posts: 0

Re: Importing Excel TIME format changes

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 )
Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation