BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dreanne
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 921 views
  • 0 likes
  • 2 in conversation