BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hello,

I am trying to write out a dataset into an excel page using the xls libname. (First time I've done it).

Here is my code:
libname xls excel '\\Besrv26\test\template4.xls' scantime=YES;
proc datasets lib=xls nolist;
delete rawdata;
quit;
data xls.rawdata;
set sums (drop=month);
where sumweek=:'02.';
run;
libname xls clear;

My problem is there is a variable which sums up amount of hours worked and it is in time8. format. It looks fine in my work dataset but then looking in the xls libname it was looking like a date so I added the scantime =YES part. Only problem is it changed the data for large amount. E.g. in sums one value is 144:15 but in rawdata it becomes 0:15:53.

One thing I have noticed is that for another varable which is number of hours having a break the figures are fine, probably because you are dealing with small numbers. e.g. 0:00:05 transfers fine. Could it be something to do with the 24 hour clock and it not liking large amounts of hours?

Many thanks for your help
2 REPLIES 2
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Maybe I misunderstand the symptom you are explaining, however I believe this is inherent with Excel and how it treats discrete time values greater than 23:59:59.00 frankly. You may want to try using another format, such as seconds in your SAS code.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Thanks for your help. I did use seconds in the end.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Discussion stats
  • 2 replies
  • 1117 views
  • 0 likes
  • 2 in conversation