DATA Step, Macro, Functions and more

Proc Import time format

Reply
Contributor
Posts: 39

Proc Import time format

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.

Trusted Advisor
Posts: 1,117

Re: Proc Import time format

Posted in reply to laneylaners

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.

Super User
Posts: 11,343

Re: Proc Import time format

Posted in reply to laneylaners

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.

Contributor
Posts: 30

Re: Proc Import time format

Posted in reply to laneylaners

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?

Respected Advisor
Posts: 3,799

Re: Proc Import time format

Posted in reply to jmhorstman

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.  

Contributor
Posts: 30

Re: Proc Import time format

Posted in reply to data_null__

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?

Respected Advisor
Posts: 3,799

Re: Proc Import time format

Posted in reply to jmhorstman

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.


Ask a Question
Discussion stats
  • 6 replies
  • 358 views
  • 2 likes
  • 5 in conversation