BookmarkSubscribeRSS Feed
rvwdv
Calcite | Level 5

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. 

 

 

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

rvwdv
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

art297
Opal | Level 21

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

 

rvwdv
Calcite | Level 5

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. 

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1371 views
  • 0 likes
  • 5 in conversation