The SAS Output Delivery System and reporting techniques

Excel autoformatting of time field

Reply
Occasional Contributor
Posts: 11

Excel autoformatting of time field

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.
SAS Super FREQ
Posts: 8,866

Re: Excel autoformatting of time field

Posted in reply to MZunnurain
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
Occasional Contributor
Posts: 11

Re: Excel autoformatting of time field

Posted in reply to Cynthia_sas
Thank you Cynthia.

This worked from within ods html....


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

Re: Excel autoformatting of time field

Posted in reply to Cynthia_sas
Now if I could just get logparse to tell me data what? or sort what?
or sql what?
Ask a Question
Discussion stats
  • 3 replies
  • 153 views
  • 0 likes
  • 2 in conversation