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
WHERE IS IT GETTING 30DEC1899 FROM ???
Also, is there a INFILE code to import this file into SAS EG. I have this file saved on my desktop (reports.xls).
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.