Importing Excel Time Formatting Issue and Converting into Numeric Seconds?

[SAS-questioner]

Hi, I tried to import excel file into SAS, and it has time. In the excel, the time format is like: 12:33:00 AM, even though I tried to change the format and remove the 'AM', but in the formula bar, the 'AM' is still there, only in the cell the 'AM' was gone. So I imported the file into SAS using "proc import", the time from 12:33:00 AM changed into 2.291666666666666669E-2 and it's character. I tried to convert it into numeric by using 'input(time,anydtdte32.)' and 'input(time, mdyampm22.)', they didn't work. Only 'input(cats(time,"00"),hhmmss.)' worked for some of the values, but didn't work for the values like "1.25E-3" (the ones without long repeating decimals like 2.29166666666666666E-3). 

I am wondering if there is a way to remove AM/PM in the excel completely not even on the formula bar? Or in the SAS, after the import, how to convert it into numeric first and then into seconds? Or convert them into seconds directly? Could anyone help me please? Thank you!

[ballardw]

Show the proc contents results for you data when " "proc import", the time from 12:33:00 AM changed into 2.291666666666666669E-2 and it's character."

Note with Proc Import, dates/times/datetime values and manually entered data: If someone creating the Excel file mixes actual Excel date/time/datetime values with manually entered character values, such as results when entering (in Excel) '12:33:00 AM then there are character values in the source and SAS will treat the whole column as character. This could also happen if the file has multiple header rows.

You might try a "File save-as" to a CSV file and import that. Typically the Excel format is exported for date/time/datetime values and the ones that may have been entered as text still appear as a "date" value. So the SAS import has a chance of getting things straight.

If the file has multiple header rows you can specify the DATAROW statement to indicate the first row of actual data. For example if the file has 3 header rows of text (even if just blank lines) use Datarow=4; to tell SAS the data values you want start on row 4 in the file.

Or look at the generated text and write your own data step to read the values.

The Mdyampm is a FORMAT, not an informat so would not work to read data and typically would show a message in the log about the attempted informat usage.

Anydtdte reads the DATE portion of a date, time or datetime values. So you wouldn't see a time result.

Anydttme would be to read the TIME portion of a datetime or time value.

Or include example data and/or log from the actual code submitted.

[SAS-questioner]

So below are the contents results for those time that imported from excel:

30 e2_duration                   Char  21 $21.   $21.     e2_duration
28 e2_end_time                   Char  18 $18.   $18.     e2_end_time
39 e4_duration                   Char  21 $21.   $21.     e4_duration
37 e4_end_time                   Char  18 $18.   $18.     e4_end_time
18 e1_duration                   Char  21 $21.   $21.     e1_duration
16 e1_end_time                   Char  21 $21.   $21.     e1_end_time

And below are example time data in the excel:

e1_end_time e1_duration	e2_start_time
3:24		  0:02:51	    4:22
4:41		  0:03:58	    6:20
 		      0:00:00	 
2:25		  0:01:35	    4:02
4:59	  	  0:02:45	    6:15
4:24		  0:02:19	    0:19
4:35		  0:03:05	    5:57

This is the time displayed in the cells after I use format, and after I clicked on each cell, the value displayed on the formula bar is: e.g., 12:02:51 AM.

Below is the example data after I imported from excel to SAS:

 Obs    e_1_start_time           e1_end_time              e1_duration
  1    2.2916666666666669E-2    .14166666666666666       1.9791666666666668E-3
  2    2.9861111111111113E-2    .1951388888888889        2.7546296296296294E-3
  3                                                      0
  4    3.4722222222222224E-2    .10069444444444443       1.0995370370370371E-3

I initially tried to use below code to convert the long decimals into time format:

format e_1_start_time e1_end_time time8.;

But it came out with errors like:

ERROR 48-59: The format $TIME was not found or could not be loaded.

So, I think I should convert the time from character into numeric, so I used the below code:

e1_duration_n=input(cats(e1_duration,"00"),hhmmss.);

But it always came out with the error for certain value like:

NOTE: Invalid argument to function INPUT at line 298 column 15.

The idea that you suggested by try file save-as to a CSV file and import sounds good, but I got hundreds of variables which is lots of work to type in each names using infile statement. Could you help me with the above problems? Thank you!

[Tom]

So this confirms the issue.  The columns in the EXCEL file have a mix of TIME values (numbers) and other things that are CHARACTER.  If you have any control over the creation of the EXCEL file see if you can get the creator to only place actual TIME values in those columns.  Then SAS will not create the variables as character and you won't have any work to do.

So if you want to convert them into actual TIME values in SAS you will need to convert the strings back into numbers.  You will need to use a different method for the strings that look like numbers, such as 1.9791666666666668E-3 and any that look like actual time style strings like:  0:02:51.  Since Excel stores time values as the fraction of 24 hours you will need multiple the resulting number by the number of seconds in 24 hours to get a SAS time value.  For the values with colons in them use the TIME informat. Note that whether there are one or two colons in the value makes a difference to the TIME informat.  If there is only one : then it assumes the two parts are hours and minutes.  If there are two then it assumes they are hours, minutes and seconds.  Check the type of strings you have and test some example values to make sure you know how to convert them.

If you need to convert a lot of variables then you probably will want to use arrays to reduce the number of lines of code needed.

data want ;
  set have;
  array strings 
e2_duration
e2_end_time
e4_duration
e4_end_time
e1_duration
e1_end_time
  ;
  array times 
e2_duration_num
e2_end_time_num
e4_duration_num
e4_end_time_num
e1_duration_num
e1_end_time_num
  ;
  do index=1 to dim(strings);
     if indexc(strings[index],':')=1 then times[index]=input(strings[index],time.)/60 ;
    else if indexc(strings[index],':')=2 then times[index]=input(strings[index],time.) ;
    else times[index]=input(string[index],32.)*'24:00't ;
  end;
  format 
e2_duration_num
e2_end_time_num
e4_duration_num
e4_end_time_num
e1_duration_num
e1_end_time_num
  time12.
;
run;

[FreelanceReinh · Accepted solution]

Hi @SAS-questioner,

I would be more comfortable with reading a CSV file (using a DATA step), as ballardw has suggested.

As to your other questions:

---

@SAS-questioner wrote:

In the excel, the time format is like: 12:33:00 AM, even though I tried to change the format and remove the 'AM', but in the formula bar, the 'AM' is still there, only in the cell the 'AM' was gone.

---

I think this has to do with locale-specific settings how times are displayed in general. For example, in my German locale the value of a cell containing 8:30 PM is displayed as 20:30:00 in the formula bar.

---

... the time from 12:33:00 AM changed into 2.291666666666666669E-2 and it's character.

---

These time values are fractions of a day. Such a character value c='2.291666666666666669E-2' can be converted to a numeric SAS time value t by

t=round('24:00't*input(c,32.),1e-9);

That is, you convert it to a numeric value, multiply by the number of seconds in a day ('24:00't=86400) and round with a suitable rounding unit to obtain accurate values. You may want to add precautions for invalid values as these would cause unwanted log messages:

_t=input(c,?? 32.);
if _t>. then t=round('24:00't*_t,1e-9);
drop _t;

[SAS-questioner]

I used your method and it becomes the numeric value, and compared with the original time, it is exactly the same seconds! Thank you!