turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Importing duration fields from Excell with HH:MM f...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-10-2017 08:04 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rvwdv

05-10-2017 08:28 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-10-2017 08:41 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rvwdv

05-10-2017 08:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-10-2017 10:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

05-10-2017 10:37 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rvwdv

05-10-2017 10:42 AM

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

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rvwdv

05-10-2017 08:55 AM

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

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rvwdv

05-10-2017 10:29 AM

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