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

[deleted_user]

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?

[Cynthia_sas]

Hi:
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:
[pre]
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 data=sashelp.shoes;
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,
product*(sales*sum);
run;
ods _all_ close;
ods listing;
title;
[/pre]

cynthia