The SAS Output Delivery System and reporting techniques

ODS to Excel differences from ver 8 to ver 9 in UNIX

Not applicable
Posts: 0

ODS to Excel differences from ver 8 to ver 9 in UNIX

I create excel output from proc tabulate using ODS directly to .xls files in UNIX SAS. In ver 8 the columns were left justified. I can replicate this in ver 9 using ods html3, but would rather find a solution using any of the new styles available in ver 9?
Posts: 9,368

Re: ODS to Excel differences from ver 8 to ver 9 in UNIX

Posted in reply to deleted_user
When you use ODS, you create either CSV, HTML or XML files that Excel knows how to open and render. They are not Excel-format "binary" files. Naming the files with a .xls extension is a convenience that allows you to double click on the file name and launch Excel.

In SAS 8, ODS HTML created HTML 3.2 compliant tags. In SAS 9, ODS HTML creates HTML 4.0 compliant tags. In SAS 9, there are also 2 new destinations that may produce better results than HTML 4.0 tags. When I use HTML 4.0 tags (such as those produced with ODS HTML), my character values in the row header area are centered instead of left-justified.

However, if I change to ODS MSOFFICE2K -- which produces Microsoft-HTML tags, then Excel left-justifies the CLASS character variable values. One of the reasons that Microsoft even has their own flavor is HTML is that they did not like the W3C HTML 4.0 tags and they had their own way of wanting to represent style attributes that differed from the HTML/CSS specification. In fact, in addition to coming up with their own flavor of HTML, Microsoft came up with their own flavor of CSS properties (if you ever see CSS style properties that start with mso-, those are Microsoft specific CSS properties -- designed specifically to work with their flavor of HTML).

Here's some code to try:
ods html3 file='c:\temp\ht3.xls' style=sasweb;
ods html file='c:\temp\ht4.xls' style=sasweb;
ods msoffice2k file='c:\temp\mso.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\xp.xls' style=sasweb;

proc tabulate;
title 'Region values will be centered in the HT4 file';
title2 'But will be left-justified in all other destinations';
class region;
class product;
var sales returns;
table region,
ods _all_ close;
ods listing;

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation