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

[robulon]

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  

[robulon · Accepted solution]

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

[robulon]

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

[Kurt_Bremser]

---

@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.

[Kurt_Bremser]

The Excel format that displays such a value correctly is

[h]:mm:ss

How do you export the report to Excel?

[robulon]

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. 

[Patrick]

@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;