transferring time data to excel using xls libname

[deleted_user]

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

[sbb]

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]

Thanks for your help. I did use seconds in the end.