Proc Import time format

[laneylaners]

This is probably a stupid question with an easy answer (I hope).  I'm trying to import an excel file with a column in it that is in time format ( 10:35:00 PM for example).  I cannot figure out how to keep SAS from changing this to a character format during import and resulting in a observation like 0.2233333.  My code is below:

***declare library for datasets***;

libname setouts "C:\Setout Report";

***import all data***;

options validvarname=v7;
proc import datafile="C:\Setout Report\Excel Files\CN Combined Raw Data.xlsx"
out=setouts.raw_data_all_time
dbms=xlsx
replace;
getnames=yes;
run;

In the excel file itself, I've tried changing the format to the excel-based 'time' format, to 'general', and 'text', and I still cannot get this to work when I import to SAS.

[FreelanceReinh]

Hi @laneylaners,

You could either read the Excel file using a data step (with an INFILE and INPUT statement and an appropriate informat specification for that time variable, e.g. TIME11.) or you have to post-process the output dataset from PROC IMPORT like in the example below:

data have;
time='0.2233333';
run;

data want;
set have(rename=(time=char_time));
time=round(86400*input(char_time, 16.));
format time timeampm.; /* or whatever time format you like */
drop char_:;
run;

Personally, I would prefer the INFILE approach.

[slacey]

Found this through google and this solution worked well for me. Especially useful for my situation where I'm importing several excel files with the time format inconsistently applied (even within the same file).

[ballardw]

Try saving the file to CSV and importing that file. Excel and SAS store time somewhat differently. SAS Uses seconds for time internally.

I believe that Excel internally uses percentage of  24 hours as decimal (1AM = 0.0141666666 = 1/24)

For 10:35 PM I would not be surprised to see 0.94 result (change display in Excel to numeric from the time display).

If you have the column set to a time display the CSV should have values like 10:35 and SAS will more likley "guess" the field should be a time value when using Proc Import.

[jmhorstman]

Hi @laneylaners,

Both of the above suggestions are workable, but you should be able to do this directly from the XLSX file using PROC IMPORT.  I just tested it on a spreadsheet and the times came in as numeric data, and PROC IMPORT even correctly associated the TIME5 format with the appropriate columns.

Is it possible that you have character data mixed in the same column?  I've found that if I have even one cell that isn't numeric (for example, a "TBD" in a list of times on a schedule), then it reads the entire column in as character data.  The character data might even be something you can't easily see, like a space in a cell at the bottom of your column.  Can you confirm that you have only numeric data in the column?

[data_null__]

---

@jmhorstman wrote:

Hi @laneylaners,

Both of the above suggestions are workable, but you should be able to do this directly from the XLSX file using PROC IMPORT.  I just tested it on a spreadsheet and the times came in as numeric data, and PROC IMPORT even correctly associated the TIME5 format with the appropriate columns.

Is it possible that you have character data mixed in the same column?  I've found that if I have even one cell that isn't numeric (for example, a "TBD" in a list of times on a schedule), then it reads the entire column in as character data.  The character data might even be something you can't easily see, like a space in a cell at the bottom of your column.  Can you confirm that you have only numeric data in the column?

---

@jmhorstman Nice to see you hangin' at the center of the SAS learning universe.  I think you've hit the nail on the head so to speak.  My tests gave similar result as yours; normally it should come over with the right type and be converted to SAS time.  

[jmhorstman]

Thanks @data_null__!  I'm trying hard to get my first accepted solution so I can get some of that nifty free swag next week at SAS Global Forum.  Will you be there?

[data_null__]

---

@jmhorstman wrote:

Thanks @data_null__!  I'm trying hard to get my first accepted solution so I can get some of that nifty free swag next week at SAS Global Forum.  Will you be there?

---

@jmhorstman and I thought your intentions were more nobel.  I will not be there but will see you at PharmaSUG.  SGF is too rich for my blood and even though I know better I like to play Craps.  I'm more Casino Party speed.  I'll post a question and select for you.

---