topic Re: Export to Excel - Number of hours rather than time of day in SAS Programming

Export to Excel - Number of hours rather than time of day

robulon — Wed, 25 Mar 2020 09:50:07 GMT

Hi,

I'm a bit stuck here and haven't been able to track a solution down so hoping for a bit of assistance.

I have written some sas code that uses proc sql to sum up telephone call times and produces a total number of hours, minutes and seconds that a team has completed each day. I have applied the time. format so get, for example, for one day I get a total of 40:34:15 which is great.

The problem comes when I look to export the report to Excel for distribution. Excel seems to be automatically applying a time of day format, so for the above example, it is displaying 16:34:15.

Can anyone suggest a way around this? Ideally I just want to be able to run the code and be in a position to send out the excel report.

Many thanks,

Rob

Re: Export to Excel - Number of hours rather than time of day

robulon — Wed, 25 Mar 2020 10:13:03 GMT

I've managed to sort this, I've used put to create the time variables as character and they are exporting into the Excel sheet as required: -

put(round(sum(talk),1),time.) as total_time

Re: Export to Excel - Number of hours rather than time of day

Kurt_Bremser — Wed, 25 Mar 2020 10:15:37 GMT

The Excel format that displays such a value correctly is

[h]:mm:ss

How do you export the report to Excel?

Re: Export to Excel - Number of hours rather than time of day

robulon — Wed, 25 Mar 2020 10:16:43 GMT

Not sure if it's forum etiquette to mark your own solution but there you go

Re: Export to Excel - Number of hours rather than time of day

Kurt_Bremser — Wed, 25 Mar 2020 10:18:59 GMT

@robulon wrote:
Not sure if it's forum etiquette to mark your own solution but there you go

That's OK, as it points any other who views the thread to something that worked. And it was you who found a viable solution.

Re: Export to Excel - Number of hours rather than time of day

robulon — Wed, 25 Mar 2020 10:28:26 GMT

Hi Kurt,

I am writing to a named range in the Excel sheet. When I opened the Excel sheet the data in the cell just showed as 16:34:15, however if I typed in the 40:34:15 and applied the [h]:mm:ss format, it showed the correct figure, so the issue seemed to be in how Excel was interpreting the data I was trying to write into it.

Re: Export to Excel - Number of hours rather than time of day

Patrick — Wed, 25 Mar 2020 10:36:56 GMT

@robulon Styles let you change the format Excel uses.

data sample;
  format hours time.;
  do hours='11:00't to '28:00't by 2999;
    output;
  end;
  stop;
run;

options missing=' ';
ods listing close;
ods excel file='~/test/sample.xlsx';
proc print data=sample noobs;
  var hours / style(column)={tagattr='format: [h]:mm:ss'};
run;
ods excel close;
ods listing;