The SAS Output Delivery System and reporting techniques

Footnote Style Excel XP

Reply
N/A
Posts: 0

Footnote Style Excel XP

in the below SAS code, how to write a footnote or note, which says the 'yellow' background color denotes 'blahblahblah'. I am thinking like, at the end of report a cell with background color of 'yellow' and other cells next to having some notes. Hope it is possible with SAS coding.

'Yellow background cell' == "these represent blahblahblah";

ods tagsets.excelxp file="C:\temp\LA_Training.xls" style=styles.myjour options(doc='Help');
options missing='0';
ods listing close;
proc report data=sashelp.class nowd ;
column age sex name weight height;
define age / order 'ID' style(column)={just=l};
define sex / display 'Job' style(column)={just=l};
define name / display 'Date';
define weight / display 'Class/Injury';
define height /sum;
break after age / summarize ;
compute sex;
if sex eq 'F' then call define(_row_,"style","style=[background=yellow]");
endcomp;
compute after age; line ' '; endcomp; run;
ods tagsets.excelxp close;
SAS Super FREQ
Posts: 8,742

Re: Footnote Style Excel XP

Hi,
If you are using a custom style template (which it seems that you are), then either changing the NoteContent style element (controls the LINE statement)
or the SystemFooter (controls the FOOTNOTE statement) would be style elements to add to your style template.

If you decide to use the LINE statement in a COMPUTE AFTER block and you did not have any other LINE statements, then you would be OK
setting a background of yellow. But, if you DO have other LINE statements (as in the code below), then you can use the changed NoteContent for the
LINE style and just set the style of your other LINE (such as between AGE) to a different style element (such as SystemTitle (which is
white background and no border lines.

The FOOTNOTE method may be more to your liking -- of course the difference between the LINE statement and COMPUTE AFTER is that a
LINE statement output will always be inside the boundaries of the table, while the FOOTNOTE statement could either be inside the worksheet
or in the footer area (depending on the value of the EMBEDDED_FOOTERS suboption.

cynthia
[pre]
ods path work.tmp(update) sashelp.tmplmst(read);

proc template;
define style styles.line;
parent=styles.journal;
class SystemFooter /
background=yellow;
class NoteContent /
foreground=red
background=yellow
font_weight=bold
font_size=8pt;
end;
run;

ods tagsets.excelxp file="C:\temp\blahyellow.xls" style=styles.line
options(doc='Help' embedded_footnotes='Yes' embedded_titles='Yes');
ods escapechar='^';
proc report data=sashelp.class nowd
style(column)={cellwidth=1.10in};
title 'TITLE: Proc Report to XML and HTML';
footnote bold c=purple h=8pt
'FOOTNOTE: The yellow highlight is because of blah blah blah blah and yada yada yada yada.';
where age in (12,14);
column age sex name weight height;
define age / order 'ID' style(column)={just=l};
define sex / display 'Job' style(column)={just=l};
define name / display 'Date';
define weight / display 'Class/Injury';
define height /sum;
break after age / summarize ;
compute sex;
if sex eq 'F' then call define(_row_,"style","style=[background=yellow]");
endcomp;
compute after age / style=SystemTitle{foreground=black background=white};
line ' ';
endcomp;
compute after;
line 'LINE: The yellow highlight is because of blah blah blah blah and yada yada yada yada.';
endcomp;
run;
ods _all_ close;
[/pre]
N/A
Posts: 0

Re: Footnote Style Excel XP

Cynthia,

This is great and one additional feature I am looking for is : in the footnote itself, two background colors. one cell with background color of yellow and no text. other cell/cells next to it showing the text "yada yada yada", without any background color or just white. Not sure about the syntax but something like footnote ' blankcell (s^ = background:yellow) yada yada yada (s^=background:white).
Thanks
SAS Super FREQ
Posts: 8,742

Re: Footnote Style Excel XP

Hi:
The issue I see with that is that the footnote is just one cell and the line is in just one cell. So, the style change that makes the entire cell background yellow is applying to the WHOLE cell.

I'm not sure that what you want to do is possible in TAGSETS.EXCELXP. If this were "regular" HTML or MSOFFICE2K, then the web browser would do what you want. But as far as I can tell, Excel will not cooperate.

The program below uses the non-breaking space (escapechar+underscore) and style overrides in the LINE and FOOTNOTE statement to do what you want --in a browser-- but if you open the resulting HTML file with Excel, the colors in the STYLE= override are not respected. HTML is not the same as Spreadsheet XML, but I believe the behavior will be the same or worse with the Excel 2003 XML, such as that created by TAGSETS.EXCELXP.

You might want to open a track with Tech Support to see whether they have any other suggestions.

cynthia

[pre]
ods msoffice2k file='c:\temp\blahyellow.html' style=sasweb;
ods escapechar='^';

proc report data=sashelp.class nowd
style(column)={cellwidth=1.10in};
title 'TITLE: Proc Report to HTML open with browser';
footnote bold c=yellow h=8pt j=l '^S={background=yellow cellwidth=1in}^_^_^_^_^_^_ ^S={foreground=purple background=white}^_^_^_The yellow highlight is because of blah blah blah blah and yada yada yada yada.';
where age in (12,14);
column age sex name weight height;
define age / order 'ID' style(column)={just=l};
define sex / display 'Job' style(column)={just=l};
define name / display 'Date';
define weight / display 'Class/Injury';
define height /sum;
break after age / summarize ;
compute sex;
if sex eq 'F' then call define(_row_,"style","style=[background=yellow]");
endcomp;
compute after age / style=SystemTitle{foreground=black background=white};
line ' ';
endcomp;
compute after / style={font_size=8pt};
line '^S={background=yellow foreground=yellow}^_^_^_^_^_^_ ^S={background=white foreground=red font_size=8pt}The yellow highlight is because of blah blah blah blah and yada yada yada yada.';
endcomp;
run;
ods _all_ close;
[/pre]
N/A
Posts: 0

Re: Footnote Style Excel XP

Cynthia,

You are right, It's not working in excel while it's doing in html. But as a work around, I am looking for mulitcolor footnote, in a single cell/mulitple cells. Actually, when I used these style in line or footnote statements with XP tagset, the excel started giving me error : 'Problem during Load',Worksheet setting' something like a pop-up box. This file cannot be opened because of erros. Errors are listed in :c\documents and settings\Local Settings\Temporary Internet Files\Content.MSO\filename.log'
SAS Super FREQ
Posts: 8,742

Re: Footnote Style Excel XP

Hi:
Generally, when you get the failure to load, it indicates some type of violation of the XML 2003 Spreadsheet "rules".

I don't know anyway around it instead of backing off the change that caused the error. You may have a reached a point where the tradeoff to get the cosmetics is not worth the effort you're putting in. In HTML, another thing I'd try is to put a picture of a yellow square next to some text. But, since the XML Spreadsheet 2003 specification (by Microsoft design) does not allow images, that won't work either.

You may want to double check with Tech Support, to be sure, but I'm not sure you can accomplish what you want.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 253 views
  • 0 likes
  • 2 in conversation