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.
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.
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.
Afraid I am not download Office files. If you could perhaps screenshot just a few records of the particular data.
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
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.
Then I'd use the solution I posted previously .. just multiply all of those numbers by 24.
Art, CEO, AnalystFinder.com
Export to csv, inspect with a text editor, then import into SAS. The xls format is notoriously unstable as a data transfer device.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.