ODS HTML File

deleted_user — Tue, 22 Feb 2011 09:08:50 GMT

I use ODS HTML to write a.xls file with a variable, say AGE_GP in a format 0-4, 5-9, 10-14, 15-19, ... An xls document is created, and the file is opened in Excel 2007, Excel will display the variable AGE_GP with the format 0-4, May.09, Oct.14, 15-19 not the range format 0-4, 5-9, 10-14, 15-19. How to fix it. Thanks.

FC

Re: ODS HTML File

Cynthia_sas — Tue, 22 Feb 2011 16:12:40 GMT

Hi:
Switching to teacher mode here for a minute...when you use ODS HTML, you are not creating a "native" or true, binary .XLS file. You are creating an ASCII text file that Microsoft Excel knows how to open and render. Even if you name the file with a .XLS extension -- the file is still HTML and ASCII text, which you can prove to yourself by opening the file with Notepad. OK...teacher clarification moment over.

Because Excel is opening an HTML table into spreadsheet view, Excel makes some assumptions (in your data's case wrong assumptions), that it should try a general numeric format for numeric columns. Because 5-9 and 10-14 looked like dates, Excel displays them as it thinks you want.

In order to "fix" the issue, you have to get Excel's attention and send it an instruction about how to format your column. With ODS HTML techniques for creating output for Excel, the method is to use the HTMLSTYLE style attribute with the MSO-NUMBER-FORMAT specification that you want. For example, if you wanted leading zeros for the variable PRODUCTID using PROC PRINT (VAR statement) or PROC REPORT (DEFINE statement), you would have:
[pre]
var productid / style(data)={htmlstyle="mso-number-format:0000000"};
define productid / style(column)={htmlstyle="mso-number-format:0000000"};
[/pre]

If you were using PRODUCTID as a CLASS variable in PROC TABULATE, then your CLASSLEV statement would be:
[pre]
classlev productid / style={htmlstyle="mso-number-format:0000000"};
[/pre]

Sometimes, Excel can be particularly frustrating -- such as with a single hyphen as you want in your string. If you run the program below, you will see that even instructing Microsoft Excel to treat the column as a TEXT format (\@), Excel ignores that format for the TESTCOL value for Alice and Alfred -- using any other separator or starting the value with a character other than space seems to finally get Excel to cooperate. Program shows using PROC REPORT, but syntax for PRINT, TABULATE is same as above.

BTW, I used the ODS MSOFFICE2K destination because ODS HTML creates HTML 4.0 tags (which Excel isn't always happy using) in favor of the Microsoft-specific HTML, as generated by ODS MSOFFICE2K. (History: When Microsoft disagreed with the W3C over the HTML specification, they formulated their own flavor of HTML.)

cynthia
[pre]
data testit;
length testcol $6;
set sashelp.class(keep=name age);
if name =: 'A' then
testcol=catx('-','5',put(age,2.0));
else if name =: 'B' then
testcol=catx('--','5',put(age,2.0));
else if name =: 'C' then
testcol=catx(':','5',put(age,2.0));
else if name =: 'H' then
testcol=catx('~','5',put(age,2.0));
else if name =: 'J' then
testcol=catx('*','5',put(age,2.0));
else testcol=catx('-','R 5',put(age,2.0));
run;

ods msoffice2k file='c:\temp\usehtmlstyle.xls' style=sasweb;

proc report data=testit nowd;
title "The \@ instructs Excel to treat the cell as TEXT";
title2 "But when you use a single - in the string does not work";
title3 "See diff between Alfred and Alice and others";
title4 "Almost any separator seems to work except a hyphen";
column name age testcol;
define name / 'Name';
define testcol / 'TestCol'
style(column)={HTMLSTYLE='text-align:left;mso-number-format:"\@"'};
run;
ods _all_ close;
[/pre]

Re: ODS HTML File

deleted_user — Wed, 23 Feb 2011 09:24:35 GMT

Thank you very much.

topic Re: ODS HTML File in ODS and Base Reporting

ODS HTML File

Re: ODS HTML File

Re: ODS HTML File