topic Re: ODS HTML to generate Excel in ODS and Base Reporting

ODS HTML to generate Excel

maggi2410 — Tue, 04 Dec 2012 00:50:24 GMT

I am facing one problem while using ODS HTML to generate excel.

This is my code

ODS HTML FILE=&fl. HEADTEXT="<STYLE> .test1 {mso-width-source:userset;width:1000pt}

.test2 {MSO-NUMBER-FORMAT:\@} </STYLE>";

proc print data=import.final label noobs;

title ;

footnote;

var a/ style={htmlclass="test1"};

var b -- z/style={htmlclass="test2"}

;

run;

ods html close;

Now

1) I have to apply character format while reading all the columns from dataset, because I hav values like 0.80, o.90 etc.

2) I have to apply the column width to the column a only, as it has VERY LONG text values.

My question is how can I apply both these properties in ODS HTML. The way I am doing it currently(shown in code above), only one of them works

Re: ODS HTML to generate Excel

Cynthia_sas — Tue, 04 Dec 2012 05:56:33 GMT

Hi:

HTMLCLASS (in my experience) works best with HTML files that follow the use of CSS Class Selectors, as outlined in the HTML 4.0 spec -- with files that are supposed to be opened by a browser. I've always found Excel to be a bit picky about CLASS selectors -- especially since ODS HTML makes HTML 4.0 compliant tags, which, generally, Excel is not very happy about opening. If you look at a Microsoft STYLE section versus HTML 4.0 style section, you will see a LOT of differences in the way the 2 style sections look.

That's why I use ODS MSOFFICE2K as the destination -- it is "Microsoft-friendly" flavor of HTML -- it is the flavor of HTML that Microsoft designed when they decided that they didn't like the HTML 4.0 specification. Rather than using CLASS selectors, I find that using a simple HTMLSTYLE attribute to pass in my Microsoft specific formats is what always works best. Microsoft has a special (different) way that it uses CLASS selectors in Microsoft version of HTML -- it is not standard CSS -- which is what HTMLCLASS is designed to produce as a style override. And, HTMLSTYLE is designed to put a style override down in the <TR> or <TD> tag, where Microsoft will (generally) respect the override.

The other thing I note is that generally, PROC PRINT "likes" for you to specify the area in the STYLE override - -such as:

style(header)={...} or style(data)={...} -- that's because some of the formats that you want to specify will or should only be applied to the "data" cells and not the "header" cells in the report.

I thought 1000 pt was too big, so in my sample program, I used 200pt in order to get all the columns on the same sheet when Excel opened the HTML file. Note that I gave the file a .XLS extension just so I could be lazy and double click from Windows Explorer and launch Excel instead of launching a browser, (if I gave an extension of .HTML). Of course, when you use .XLS as the file extension, you are not creating a true, binary Excel file -- you are just fooling the Windows registry into launching Excel when the file icon is double clicked. You can look at the output file in Notepad to see that it reallly is HTML in the output file created by ODS.

The screenshot shows all the code I submitted and the output opened in Excel. Note that all my numeric columns were treated as character and that my NAME column is bigger than the default width. The techniques that I show in this program were outlined in my SAS Global Forum paper in 2011: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia

Re: ODS HTML to generate Excel

maggi2410 — Tue, 04 Dec 2012 06:49:48 GMT

Thankyou so much Cynthia. Things seem to work now for me. But now the problem is I am getting the background color as Gray. But I want it to be white. How can I change it?

I guess for Proc report the code is:

style(report)={background=white}

But how and where should I put in it proc print, ie in the type of code , you put it in your screen shot.

Thanks in advance.

Re: ODS HTML to generate Excel

Andre — Tue, 04 Dec 2012 11:51:39 GMT

Maggi

proc print data=... style(table)={background=white}

is i think the answer for proc print

Andre

Re: ODS HTML to generate Excel

maggi2410 — Tue, 04 Dec 2012 18:27:17 GMT

Hi ANdre. That doesnt work.

I dont know how to get teh background as white. I am using the foloowing code:

ODS msoffice2k FILE=&fl. ;

proc print data=inds label noobs style(report)={background=white};

var a/style(data)={htmlstyle="width:'500pt'"} ;

var b -- c/ style(data)={htmlstyle="mso-number-format:'\@';"};

run;

Can nayone help me on this.

Thanks!

Re: ODS HTML to generate Excel

Cynthia_sas — Tue, 04 Dec 2012 18:38:32 GMT

Maggi:

Did you copy my code? If you used the MSOFFICE2K destination and style=sasweb, you should get a white background under the table. But, the rest of the cells in the sheet -- the "other" cells not bounded by the table will be gray, that is an artifact of Excel and how it opens HTML markup files. If you want the rest of the background, of the "unused" cells to be white, I think you have to change the style template.

Or, switch to using TAGSETS.EXCELXP -- by default, the unused cells will be white . The syntax will be slightly different.

cynthia

ods tagsets.excelxp file='c:\temp\xpclass.xml' style=sasweb

options(absolute_column_width='30,10,10,10,10');

proc print data=sashelp.class label noobs;

var name;

var age -- weight/style(data)={tagattr="FORMAT:@"};

run;

ods _all_ close;

Re: ODS HTML to generate Excel

maggi2410 — Tue, 04 Dec 2012 19:15:20 GMT

Thanks again Cynthia. Its my bad. Actually I was using sasweb with proc print, instead of using it with proc.

Anywaz, now I used style=styles.minimal. And everything worked for me.

Thanks for ur help.