New to ODS: How to conserve formatting when exporting to Excel?

[dreanne]

Hello,

I'm relatively new to ODS, so I'm a bit lost here.

I use ODS and Proc Template to create a table that is "publication-ready". Now, I don't know how to export it to Excel so that I don't lose the data format. I could always copy-special paste column by column, but there must be a better way, right?

I attach a simpler version of the code I run and output it produces. (The full version has about 25 columns) Specifically, I would like to be able to export both columns simultaneously in Excel without them collapsing one on top of the other (which a copy-special paste does); and I would like to conserve the format, namely, every other observation in the second column needs to be in parentheses.

Thank you for your help!

[Cynthia_sas · Accepted solution]

Hi:

  Sadly, you are experiencing the world of Microsoft defaults. When Excel opens your output file (whether you use HTML or TAGSETS.EXCELXP), you are creating an ASCII text file that Microsoft opens using ALL the defaults that it wants to use. And, generally, (as you have discovered), ignoring most of the formatting for numbers (such as leading zeros, percents, decimal places and parens and column widths) that you work hard to get in SAS.

  When you use ODS methods to get output into Excel, you have the choice to allow Excel to use its defaults or you can send a Microsoft format (not a SAS format) to Excel. The style attribute that you use will depend on your ODS method:

1) HTML based methods: use HTMLSTYLE= attribute with MSO-NUMBER-FORMAT (Microsoft format) value specified

2) XML based methods: useTAGATTR= attribute with a Microsoft format value specified

  Look in this user group paper for some concrete examples: Also, you can search other forum postings for these techniques.

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

  Also, I'm not sure you really need a TABLE template. I think you can do most of what you show with PROC REPORT. But you'll have to investigate how to use style overrides with PROC TEMPLATE since my paper doesn't talk about that part of using ODS styles.

  Last, but not least, I rarely use ODS HTML when I create files for Excel. Microsoft developed their own "flavor" of HTML and ODS can create that "flavor" of HTML using ODS MSOFFICE2K.

cynthia

[dreanne]

Thank you!

I'll read through the pdf that you linked and let you know how it works.

EDIT: It worked perfectly! Thank you so much, the linked document was very useful.