SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

transferring time data to excel using xls libname

Reply
N/A
Posts: 0

transferring time data to excel using xls libname

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
Super Contributor
Super Contributor
Posts: 3,174

Re: transferring time data to excel using xls libname

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.
N/A
Posts: 0

Re: transferring time data to excel using xls libname

Thanks for your help. I did use seconds in the end.
Post a Question
Discussion Stats
  • 2 replies
  • 133 views
  • 0 likes
  • 2 in conversation