Problem controlling output format in ods html

doakley — Tue, 31 Jan 2012 15:11:38 GMT

1) I am having trouble controlling the format of some output from SAS to an Excel file: for instance, when I'm using age-groups as my variable, the column or row label 10-14 is routinely interpreted as a date (14-Oct). Here's the code:

ods html path="c:\temp" (url=none) file="&YEAR._I11.xls" style=minimal;

PROC PRINT NOOBS LABEL DATA=MD6H;

TITLE1 'TABLE I11';

TITLE2 'DIVORCES BY AGE-GROUP OF HUSBAND, BY AGE-GROUP OF WIFE';

TITLE3 "KANSAS, &YEAR";

FORMAT HUS_AGE AGE_A.;

LABEL HUS_AGE='AGE-GROUP OF HUSBAND'

The label on COL2 is reinterpreted as a date. If I try COL2="'10-14" [double quote,single quote,10-14,double quote], the output is '10-14, with a visible quote mark. Same if I use COL2='''10-14' [single quote,single quote,single quote,10-14,single quote]. The same problem exists for rows when this age-group comes in through the FORMAT statement.

2) How do I use ods to put a header over a set of Excel columns?

Thanks,

David

COL1='TOTAL' COL2='10-14' COL3='15-19' COL4='20-24'

COL5='25-29' COL6='30-34' COL7='35-39'

COL8='40-44' COL9='45-49' COL10='50-54' COL11='55-59'

COL12='60-64' COL13='65-69' COL14='70-74' COL15='75-79'

COL16='80 & Over' COL17='N.S.';

RUN;

ods html close;

Problem controlling output format in ods html

Cynthia_sas — Tue, 31 Jan 2012 16:15:09 GMT

Hi:

You will have to send a Microsoft format from SAS to Excel. Since you are using HTML-based techniques to create a file for Excel, this points to 2 changes to your code:

1) I would recommend using ODS MSOFFICE2K as the destination instead of ODS HTML. ODS HTML destination creates HTML 4.0 "flavor" of HTML tags, which Microsoft doesn't always like. On the other hand, ODS MSOFFICE2K creates Microsoft HTML "flavor" of HTML tags (yes, Microsoft created their own flavor of HTML).

2) When Microsoft created their own "flavor" of HTML, they also created their own "flavor" of CSS style properties that you can use to send Microsoft formats to Excel. for HTML-based files, these correspond to using mso-number-format style property with the HTMLSTYLE attribute.

What you need to do is tell Excel to treat your column as TEXT, not as a number (or date). So the "magic" character that does this is the @, which represents text in a Microsoft format.

I show an example of this on pages 2-4 of this paper.

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf (look at the HTMLSTYLE override example for the ISBN variable).

cynthia

topic Problem controlling output format in ods html in ODS and Base Reporting

Problem controlling output format in ods html

Problem controlling output format in ods html