The SAS Output Delivery System and reporting techniques

ODS ExcelXP Tagset & Very Long Titles

Reply
Contributor
Posts: 52

ODS ExcelXP Tagset & Very Long Titles

Two questions that I believe are related to the ExcelXP tagset.

1) I have one of those really long legal titles that takes up row 5 of an Excel spreadsheet. I've mimicked the title below and the carriage returns that I need, but can't figure out how to obtain with SAS.
2) The title that is returned isn't half bad, although the client will object. When I open the spreadsheet, the title is partially hidden, and I need to do a manual fix. For that particular title, I almost need an Excel row height of 55. Any options that I've overlooked?

TIA,
Kim LeBouton

%let title5="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz";
ods html close;
ods tagsets.excelxp file='c:\test long titles.xls';

ods tagsets.excelxp options(embedded_titles='yes'
gridlines='yes'
autofit_height='yes');

ods tagsets.excelxp options(sheet_name='XXX XXXXX');

title j=center font='Century Gothic' height=8pt &title5;
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;
SAS Super FREQ
Posts: 8,740

Re: ODS ExcelXP Tagset & Very Long Titles

Hi:
Well, for some reason, I had to put just=c into a style template. I could not get centering working with the center command in the TITLE statement or in the ESCAPECHAR style override.

I -did- manage to get the HEIGHT working for the TITLE line. If you say autofit_height='no' then you can set a specific row height for the title using the ROW_HEIGHTS suboption.

But then, since the title needs to span ALL the columns, I had odd wrapping for the XXX, YYY and ZZZ -- so I needed to increase the width of every column so that all the columns together would be wide enough to fit the long text.

Last, I had to use the special string that represents Alt+Enter for Excel -- that is the 
 string. Excel only allows you to specify a "return" in a cell by typing Alt+Enter. Apparently, the way you specify the equivalent of Alt+Enter in a Spreadsheet Markup Language XML file is



...and so that special string needed to have protectspecialchars set to off so the & would not get turned into &amp; when it was used. (Just like < gets "protected" by being turned into &lt; and > gets turned into &gt; )

cynthia

[pre]
options noquotelenmax;

ods path work.ttt(update) sasuser.templat(update) sashelp.tmplmst(read);

proc template;
define style styles.kb;
parent=styles.default;
class SystemTitle from SystemTitle /
just=c;
end;
run;

%let t1=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
%let t2=yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy;
%let t3=zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz;

ods _all_ close;

ods tagsets.excelxp file='c:\test long titles.xls' style=styles.kb
options(embedded_titles='yes'
gridlines='yes'
autofit_height='no'
row_heights='0,0,0,50,0,0,0');

ods tagsets.excelxp options(sheet_name='XXX XXXXX');
ods escapechar='^';

title f="Century Gothic" h=8pt "^{style [protectspecialchars=off font_size=8pt]&t1%str(&#10Smiley Wink&t2 %str(&#10Smiley Wink&t3 }";
proc print data=sashelp.class
style(column)={cellwidth=1.75in};
run;
ods _all_ close;
[/pre]
Contributor
Posts: 52

Re: ODS ExcelXP Tagset & Very Long Titles

My title is all in the A1 cell and does not span the other columns.

I'm using Excel 2003, SAS 9.2 (2M2), and excelxp tagset (8/25/10).

TIA,
Kim
Contributor
Posts: 52

Re: ODS ExcelXP Tagset & Very Long Titles

A follow-up.

I was able to get this to work with the merge_titles_footnotes='yes'. I was able to turn off the style=.

options
(embedded_titles='yes'
embedded_footnotes='yes'
gridlines='yes'
autofit_height='no'
merge_titles_footnotes='yes'
row_heights='0,0,0,15,0,0,0');
ods tagsets.excelxp options(sheet_name='XXX XXXXX');
ods escapechar='^';
title f="Century Gothic" h=8pt "^{style [protectspecialchars=off font_size=8pt]&t1%str( )&t2 %str( )&t3 }";
title2 test test;
footnote footnote1;
proc print data=sashelp.class style(column)={cellwidth=1.75in};
sum height;
run;
ods _all_ close;

Thanks for your write-up. It was very, very helpful.

Kim LeBouton
SAS Super FREQ
Posts: 8,740

Re: ODS ExcelXP Tagset & Very Long Titles

Hi:
When I used Office 2007 to open the file generated by my code, the title did span the entire table. It must be something different between 2003 vs 2007. I did not have to use the Merge_titles_footnotes suboption -- but it's nice to know that it will work in a situation like this!

cynthia
Occasional Contributor
Posts: 14

Re: ODS ExcelXP Tagset & Very Long Titles

What is between style and &t1? I tried =, space and nothing, none of them work perfectly.

style Re: ODS ExcelXP Tagset & Very Long Titles&t1

Occasional Contributor
Posts: 14

Re: ODS ExcelXP Tagset & Very Long Titles

figured it out myself. add a style in between should work

^{style &t1...}

Contributor
Posts: 52

Re: ODS ExcelXP Tagset & Very Long Titles

This has become one of my favorite techniques, but one other question.

What if my very long titles have special characters?

For example, %t1, %t2 and %t3 become as follows:

%let t1=xx "xxxxx" xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;%let t2=yyyyyy's yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy;%let t3=zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz;

TIA,
Kim LeBouton
SAS Super FREQ
Posts: 8,740

Re: ODS ExcelXP Tagset & Very Long Titles

Hi:
I'm not sure. Do your very long titles REALLY have NO spaces between the letters except in a few places???

I'm guessing that you'd have to use macro quoting functions to "protect" the special characters. Or sometimes I take the easy way out and try the HTML character entities with a simple %STR or %NRSTR. I haven't tested them all, but Excel seems OK with &quot; for double quote marks....and some of the others that I used here:
[pre]
%let t1=%str(Mary Poppins%nrstr(&regSmiley Wink says %nrstr(&quotSmiley Winksupercalifragilisticexpealidocious%nrstr(&quotSmiley Wink.);
%let t2=%nrstr(I love the chimney sweep%'s song Chim-Chim-Cheree);
%let t3=%str(zz%nrstr(&oslashSmiley Winkzzz%nrstr(&copySmiley Wink zzzzz %nrstr(&szligSmiley Wink zzzz zzzz %nrstr(&oumlSmiley Wink zzz z z zzz zzzz zzzz zz);
[/pre]

which shows up for me as:
Mary Poppins® says "supercalifragilisticexpealidocious".
I love the chimney sweep's song Chim-Chim-Cheree
zzøzzz© zzzzz ß zzzz zzzz ö zzz z z zzz zzzz zzzz zz

cynthia
Ask a Question
Discussion stats
  • 8 replies
  • 2781 views
  • 0 likes
  • 3 in conversation