The SAS Output Delivery System and reporting techniques

ODS HTML file= test2.xls Footnote starts in Column B?

Reply
N/A
Posts: 0

ODS HTML file= test2.xls Footnote starts in Column B?

Im not overly familiar with how all of ODS works. Here is my code :

ods html file= "v:\test2.xls" headtext= ""

proc report data= test nowd;
Column site_no;

define site_no /'Site #' style(column)={background=white foreground=black font_face='Ariel' font_size=8pt} style(header)={foreground=black background=white font_face='Ariel' font_size=10pt};

footnote1 "Run Date: &sysdate9.";

The excel output has the footnotes starting in column B? Any info as to how I can truly left justify the footnotes to start in column A? Message was edited by: chris2112
N/A
Posts: 0

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to deleted_user
Not sure why format on my original message is messing up.

Here is footnote code:

footnote3 "Run Date: &sysdate9.";
SAS Super FREQ
Posts: 8,865

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to deleted_user
Hi:
The < and > symbols in open code tend to confuse the forum posting mechanism. For more information on posting code and how to mask these symbols and maintain the code indenting, see this previous forum posting:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

cynthia

PS... as an FYI Microsoft is not always happy about reading and rendering ODS HTML output because in SAS 9, the level of HTML that is created is HTML 4.0 and Microsoft sort of parted ways with the W3C on HTML 4.0. There are 2 destinations that Microsoft is happier with and these are:
-- ODS HTML3 (creates HTML 3.2 compliant tags)
or
-- ODS MSOFFICE2K (creates Microsoft HTML compliant tags)
N/A
Posts: 0

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to Cynthia_sas
Ok here it is again. I actually was using tagsets.msoffice2k first but can't get that to left justify or take on my font size. I am ver unfamiliar with proc template so was hoping for an easier way. I found something online that states that it will always start in column be due to an anchor?

ods html file= "v:\test2.xls" headtext= "[style>] td {mso-number-format:\@}[/style]"

proc report data= test nowd;
Column site_no;

define site_no /'Site #' style(column)={background=white foreground=black font_face='Ariel' font_size=8pt} style(header)={foreground=black background=white font_face='Ariel' font_size=10pt};

footnote1 "[td align=left foreground=black colspan=5][fontsize=8]Run Date: &sysdate9.[/font][/td]";
SAS Super FREQ
Posts: 8,865

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to deleted_user
Hi:
Hmmm -- I never see the footnote start in Column B. When I run this code -- slightly altered from your version:
[pre]

data test;
length site_no $20;
set sashelp.class;
if sex = 'F' then site_no = put(age,2.0)||put(int(height),2.0)||'001';
else if sex = 'M' then site_no = put(age,2.0)||put(int(height),2.0)||'002';
run;

ods listing close;

ods html file='html4.xls' style=sasweb;
ods msoffice2k file='mso.xls' style=sasweb;
ods html3 file='html3.xls' style=sasweb;

proc report data=test nowd;
title1 'The Default Title';
title2 h=14pt j=l c=red 'RED' j=c c=blue 'BLUE' j=r c=green 'GREEN';
footnote1 j=l c=black h=14pt "Run Date: &sysdate9.";

Column site_no;
define site_no /'Site #'
style(column)={background=white foreground=black font_face='Ariel' font_size=8pt
htmlstyle="mso-number-format:\@"}
style(header)={foreground=black background=white font_face='Ariel' font_size=10pt};
run;
ods _all_ close;
[/pre]

This is what I observe:
--HTML (HTML 4.0 tags): Title 1 starts in Row1, colA; title2 uses multiple cells on one line; footnote starts in column A; site_no is text in Excel

--MSOFFICE2K (Microsoft HTML tags): Title1 appears in Row 1, centered above table; title2 uses 3 lines; footnote starts in column A; site_no is text in Excel

--HTML3 (HTML 3.2 tags): Title starts in Row 2, approximately centered above table; title2 uses 1 line; footnote starts in column A; site_no is text in Excel

Here's why I changed your code:
1) there's no need to "send" a <style> section in HEADTEXT to change TD because a) other pieces of the output might have TD tags that could be adversely affected by this style change and b) you can pass in an mso-number-format style property via the HTMLSTYLE= attribute and the HTMLSTYLE attribute method works with all 3 destinations above (HTML, HTML3 and MSOFFICE2K)

2) rather than use HTML tags for the footnote, I used the font, color and size options that were built into ODS. Note in the observations above that each destination creates different tags and Excel interprets each set of HTML tags in a different manner.

You can easily left-justify title and footnote strings by using J=L in the TITLE or FOOTNOTE statement. (you can see in the output that J=C or J=R is interpreted a bit differently by Excel --even though it works beautifully in a browser.) For size changes in the title and footnote, you would use the H= attribute. For font changes, you would alter the TITLE or FOOTNOTE statement to have a font reference:
[pre]
title1 j=l font='Courier New' h=14pt c=purple 'The Default Title';
[/pre]

When you create HTML files that can be opened and rendered in Excel, remember that even if you give the file a ".xls" extension, you are not creating a true, binary Excel file. You are merely creating an ASCII text file that Excel knows how to open and render. Because you use a file extension of ".xls" when you double click on the file icon, Excel will automatically launch because that extension is associated with Excel -- so basically you're fooling the Windows registry because otherwise a browser would launch if you clicked on a file with an ".html" or ".htm" file extension.

cynthia
N/A
Posts: 0

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to deleted_user
Yes, when I add what you sent it does put it into the column A. The reason I had used the [td] code with my footnotes is that I want the footnote to span across the columns and not wrap text in colun A. I found that colspan= that lets you span the footnote but this is what sends it to column B. What you gave me is much cleaner and makes more sense, but I am still at a loss as to how to span the footnote.
SAS Super FREQ
Posts: 8,865

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to deleted_user
This may turn out to be a question for Tech Support. I frequently find that chasing cosmetics between HTML and Excel is more trouble than it's worth.

cynthia
N/A
Posts: 0

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to Cynthia_sas
I agree with you. I really appreciate your time..thanks for the help
N/A
Posts: 0

Re: ODS HTML file= test2.xls Footnote starts in Column B?

Posted in reply to deleted_user
Hi chris2112.

I'm using SAS9.2 with Office 2007, and generating .XLS output via ODS HTML3. I was having the same trouble as you with my footnote starting in the second column when starting with "
I took a look at the HTML that was generated and noticed that just before my " cellpadding="1" rules="none" frame="void" border="0" summary="Page Layout">


[/pre]

I.e., the template's class for system title and footnote kicks in after the table containing the data generates, starts a new container for the footnote in a new table, and starts a new row with an open "" tag defining the first cell as a centered footer.

My "trick" is to clip the "" with an end "" and end the row with an end "" before any of my footnote text, and then just start a new row with my footnote text, like so:

[pre]




test.sas
Generated on Saturday, June 19, 2010


[/pre]

Not pretty, for sure, but it does seem to do the trick.

But I definitely agree with Cynthia, Tim, and others who have posted on this: best to use a "new" Excel-aware tagset like tagsets.ExcelXP... SOOO much easier to deal with. Perhaps, like me, you are forced to support a legacy version of code and use HTML3?

[pre]
[/pre]
- Tzvi is me
Ask a Question
Discussion stats
  • 8 replies
  • 755 views
  • 0 likes
  • 2 in conversation