DATA Step, Macro, Functions and more

Importing duration fields from Excell with HH:MM format

Reply
New Contributor
Posts: 3

Importing duration fields from Excell with HH:MM format

I'm new to SAS and trying to change the formatting of a variable that I'm importing from Excel. The variable format in Excel is HH:MM but when it imports to SAS it's displayd as a number such as 0.4889556658666

 

It's supposed to be a duration of a shift. 

 

How can I change the format when importing the table to an actual duration that I can use in creating managment reports? 

 

I'd like to create a report per customer that indicates the total number of hours our employees spent at one of their locations.

 

Please help. 

 

 

Super User
Super User
Posts: 7,413

Re: Importing duration fields from Excell with HH:MM format

How are you importing it?  Can you provide an example of what the data actually looks like for 0.4889556658666.  Excel, like SAS stores times/dates as offsets from a base value.  So days since 1950 maybe for dates (can't remember the exact year).  The 0.488... is what the actual data is, unformatted.  So you would need to know the conversion is.  Proc import is very basic really.  If you want to get data in a repeatable, defined way, move to a delimited file format (CSV or something) and write a datastep import, that way you can control it all.  Alternatively, in your Excel file, set the format of those variables to text, then SAS will read them in as text and you can conver then.  

New Contributor
Posts: 3

Re: Importing duration fields from Excell with HH:MM format

Hi, thanks for the reply. I've uploaded an example of the excel file. If I straight import it itno SAS studio I get the 0.488... numbers indeed, but in Excel it's an adjusted field. It's related to the "Werkniveau" fields.

Super User
Super User
Posts: 7,413

Re: Importing duration fields from Excell with HH:MM format

Afraid I am not download Office files.  If you could perhaps screenshot just a few records of the particular data.

PROC Star
Posts: 7,363

Re: Importing duration fields from Excell with HH:MM format

Depends upon what the person(s) entering the data really tried to represent. The Excel format attached to the fields indicate that the numbers represent the time (i.e. hh:mm), thus 16:30 is representing 4:30pm. You have to find out what that means. Does it represent 4:30pm, or 16 hours and 30 minutes, or 16 minutes and 30 seconds?

 

Proc Import imported 16.30 was imported as .6875 or 16.5 hours/24. Thus, if you multiply all of your numbers by 24, you'll get hours and fractions of hours (e.g., .6875*24=16.5.

 

Art, CEO, AnalystFinder.com

 

New Contributor
Posts: 3

Re: Importing duration fields from Excell with HH:MM format

the data is supposed to represent duration. So not a specific time, but how long an employee spent in total working for a specific customer. 

 

This could be something like: 

Employee A went to customer B 7 times during the month and spent a total of 120 hours working for the. I'm interested in the 120 hours... and need to do calculations with this in order to determine how many hours were spent there in total by all employees and such. 

PROC Star
Posts: 7,363

Re: Importing duration fields from Excell with HH:MM format

Then I'd use the solution I posted previously .. just multiply all of those numbers by 24.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 6,962

Re: Importing duration fields from Excell with HH:MM format

Export to csv, inspect with a text editor, then import into SAS. The xls format is notoriously unstable as a data transfer device.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,502

Re: Importing duration fields from Excell with HH:MM format

I believe Excel stores time as a fraction of a day. So mulitply by 24 hours worth of seconds to get it converted to number of seconds.

2103  data x;
2104    excel=0.4889556658666 ;
2105    sas='24:00't * excel;
2106    put excel= sas= sas time8. ;
2107  run;

excel=0.4889556659 sas=42245.769531 11:44:06
Ask a Question
Discussion stats
  • 8 replies
  • 172 views
  • 0 likes
  • 5 in conversation