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.
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.
05-10-2017 08:41 AM
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
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.
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.
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