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,864

Re: ODS ExcelXP Tagset & Very Long Titles

Posted in reply to KimLeBouton
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

Posted in reply to Cynthia_sas
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

Posted in reply to KimLeBouton
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,864

Re: ODS ExcelXP Tagset & Very Long Titles

Posted in reply to KimLeBouton
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

Posted in reply to KimLeBouton

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

Posted in reply to KimLeBouton
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,864

Re: ODS ExcelXP Tagset & Very Long Titles

Posted in reply to KimLeBouton
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
  • 2889 views
  • 0 likes
  • 3 in conversation