Code discrepancies between 9.1 and 9.2

[AUTigers]

Hi,

when the following code was run under SAS9.1, the leading zero was preserved. however, the style options was totally ignored when it run on SAS9.2. Can you please let me know what has been changed since 9.1?

platform: SAS9.2 and SAS9.1 on Windows XP pro,
Excel2003

thanks!

Yu

data one;
input (acc_no zipcode) ($);
cards;
0111 023560
0333 023334
;
run;
ods html file='temp.xls';
proc report data=one nowd;
column acc_no zipcode;
define acc_no/display style={htmlstyle="mso-number-format:\@"};
define zipcode/display style={htmlstyle="mso-number-format:\@"};
run;
ods html close;

[Cynthia_sas]

Hi:
I no longer have SAS 9.1.3 or Office 2003 to test with, but the following code works as I expect in Office 2007:

1) ODS MSOFFICE2K -- shows leading 0 (with green triangle in the Excel cell) ODS MSOFFICE2K is Microsoft-flavor of HTML -- so it is most compatible with Office products since it was designed as the HTML spec for Office by Microsoft.

2) ODS HTML3 -- shows leading 0 (with green triangle [The number in this cell is formatted as text...] in the Excel cell for character variable) ODS HTML3 creates HTML 3.2 compliant HTML tags -- which Microsoft renders well.

3) ODS HTML -- does NOT show leading zeroes anywhere -- this is actually what I expect in 9.2 and I believe this worked the same way in SAS 9.1. If you used the registry in SAS 9.1 to always generate ODS HTML3 -- but did NOT set the registry switch in SAS 9.2, then it would explain the apparent behavior.

In my program, I show how to use mso-number-format property for a numeric variable to display leading zeroes in Excel (and not have the green triangle in the cell.)

[pre]
data one;
input (acc_no zipcode) ($);
numvar = input(acc_no,4.);
return;
cards;
0111 023560
0333 023334
0044 012345
;
run;

ods msoffice2k file='c:\temp\lead_zero_mso.xls' style=sasweb;
ods html3 file='c:\temp\lead_zero_ht3.xls' style=sasweb;
ods html file='c:\temp\lead_zero_ht4.xls' style=sasweb;
proc report data=one nowd;
title 'Leading Zeroes as Character Var or Numeric Var';
column acc_no zipcode numvar;
define acc_no/display style={htmlstyle="mso-number-format:\@"};
define zipcode/display style={htmlstyle="mso-number-format:\@"};
define numvar / display style={htmlstyle="mso-number-format:0000"};
run;
ods _all_ close;
[/pre]

If you want to pursue the issue about 9.2 versus 9.1.3, you'll have to work with Tech Support, because they will have both versions of SAS available for testing and comparison.

cynthia

[AUTigers]

thanks, Cynthia.

I will open a track with SAS Tech. it seems to me that there are some changes made for SAS9.2. because the code was written long time ago and we never had any problems with it.

Yu

[Cynthia_sas]

Hi:
I understand your point of view. If the code was written a LONG time ago (like in SAS 8, then it would have been OK because SAS 8 and ODS HTML created HTML 3.2 compliant tags by default. Microsoft "likes" 3.2 compliant HTML tags. So that code would have worked in SAS 8.2 with ODS HTML.

Then, in SAS 9, ODS HTML started creating HTML 4.0 tags by default. Many web browsers were not ready for HTML 4.0 (because of inline <STYLE> section) and there was a registry switch you could flip in SAS to automatically create 3.2 tags with the simple ODS HTML syntax.

A lot of folks who used SAS/IntrNet did this because it was easier for them to flip the switch on the Application Server in SAS 9 then to recode all their programs to invoke ODS HTML3. Microsoft parted ways with the W3C folks over HTML 4.0 and that's actually about the time (2000) when Microsoft came up with their own flavor of HTML. So it is possible that your folks "flipped the registry switch" with SAS 9.0 or SAS 9.1 instead of recoding everything to ODS HTML3.

Honestly, I don't think that HTML 4.0 tags with mso-number-format as a style property ever opened in Excel correctly -- but you'll have to check with Tech Support on that. As I said, I no longer have 9.1.3 to test with. The mso-number-format and other mso- style properties were created by Microsoft about the same time they created their own flavor of HTML.

So the first time I ever used mso-number-format, was with ODS HTML3 and ODS MSOFFICE2K -- both are HTML "flavor" of destinations that create the kind of HTML tags and style definitions that Microsoft Office is happy with. For example, if you run the code that I previously posted, using STYLE=SASWEB, you will see that the ODS HTML3 and the ODS MSOFFICE2K output files both use the blue and white style specifications when the files are opened in Excel; however, ODS HTML (the 4.0 tags) style looks icky in Excel, mostly because Excel does not like the HTML 4 in-line style section.

cynthia