BookmarkSubscribeRSS Feed
msas
Calcite | Level 5

I am using the msoffice2k_x tagset to create an Excel workbook with multiple sheets. I have been successful in creating the workbook, but I am having difficulty figuring out how to change a couple things on my sheets.

For instance, if a value is '1-2' in my SAS output, it ends up in Excel looking like 2-Jan. It has been changed to a date value. How do I get around that?

Also, the titles don't always align with my tables correctly in Excel. If the table is small, then the title will be much longer than the table. Is there a way to make it only the width of the table? Or at least to center it?

Any assistance is appreciated.

1 REPLY 1
Cynthia_sas
SAS Super FREQ

Hi:

   Excel has defaults for how it opens and renders the file that you create with ODS MSOFFICE2K_X. That destination creates an ASCII text file -- an HTML file -- that Excel knows how to open. But in the rendering department, Excel makes certain assumptions about column width, column type and column format that you may or may not want.

  The way around this default Excel behavior is for you to send a specific Microsoft format instruction using the mso-number-format CSS style property. The way you send this style information to Excel from SAS is through the use of the HTMLSTYLE= style attribute.

  There have been many previous forum postings about how to specify and use HTMLSTYLE in a STYLE= override; and the doc for MSOFFICE2K_X also shows an example under the topic "Formatting Data"

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#formatting (see how AGE and HEIGHT  are each formatted with 2 decimal places)

  In addition, this SAS Global Forum paper has extensive examples:

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

cynthia

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
  • 1 reply
  • 741 views
  • 3 likes
  • 2 in conversation