topic Re: Excel autoformatting of time field in ODS and Base Reporting

Excel autoformatting of time field

MZunnurain — Sun, 21 Mar 2010 00:00:40 GMT

I have an output time from %logparse macro which appears like this in
the csv file....

10:31:34.90 Representing 10 hours 31 minutes 34.90 seconds from the
log

When I open the csv file using Excel it appears like this...

31:34.9 without the 10 hours

when I click on the cell, a format showing as...

10:31:35 AM

Excel took its best guess, but this is not how I would like the time
to appear. I would like it to appear as 10:31:34.90 as in the csv
file. Any ideas? I can reformat myself using hh:mm:ss.000 but as
this will be used for multiple spreadsheets I would like it to be
automatic.

Re: Excel autoformatting of time field

Cynthia_sas — Sun, 21 Mar 2010 02:02:39 GMT

Hi:
When you use ODS CSV to create a report file that Excel can open, you are going to get all the Excel defaults. If you don't like the default format (column width, number format, etc) that Excel uses when it opens the CSV file -- you can use the pull-down menus -inside- Excel to change them.

There is a way to send a Microsoft format from ODS to Excel -- if you use
1) ODS HTML or ODS MSOFFICE2K destinations (HTML-based destinations), then you can use the HTMLSTYLE= style attribute to send an mso-number-format CSS style property to Excel (to use when it opens the HTML file);
2) ODS TAGSETS.EXCELXP destination (Spreadsheet markup language XML based destination) then you can use the TAGATTR= style attribute to send a Microsoft formula or format to Excel (to use when it opens the XML file).

There is a discussion and examples of both techniques in this paper:
http://www2.sas.com/proceedings/sugi28/012-28.pdf

cynthia

Re: Excel autoformatting of time field

MZunnurain — Mon, 22 Mar 2010 16:44:03 GMT

Thank you Cynthia.

This worked from within ods html....

var realtime / style={htmlstyle="vnd.ms-excel.numberformat:hh:mm:ss.00"};

Re: Excel autoformatting of time field

MZunnurain — Tue, 23 Mar 2010 18:42:17 GMT

Now if I could just get logparse to tell me data what? or sort what?
or sql what?