topic ODS ExcelXP Tagset & Very Long Titles in ODS and Base Reporting

ODS ExcelXP Tagset & Very Long Titles

KimLeBouton — Sat, 18 Sep 2010 01:18:08 GMT

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;

Re: ODS ExcelXP Tagset & Very Long Titles

Cynthia_sas — Sat, 18 Sep 2010 03:44:43 GMT

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 & when it was used. (Just like < gets "protected" by being turned into < and > gets turned into > )

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(
)&t2 %str(
)&t3 }";
proc print data=sashelp.class
style(column)={cellwidth=1.75in};
run;
ods _all_ close;
[/pre]

Re: ODS ExcelXP Tagset & Very Long Titles

KimLeBouton — Tue, 21 Sep 2010 22:42:43 GMT

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

Re: ODS ExcelXP Tagset & Very Long Titles

KimLeBouton — Wed, 22 Sep 2010 00:39:29 GMT

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

Re: ODS ExcelXP Tagset & Very Long Titles

Cynthia_sas — Wed, 22 Sep 2010 14:57:12 GMT

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

Re: ODS ExcelXP Tagset & Very Long Titles

KimLeBouton — Thu, 28 Oct 2010 23:06:51 GMT

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

Re: ODS ExcelXP Tagset & Very Long Titles

Cynthia_sas — Fri, 29 Oct 2010 01:10:34 GMT

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 " for double quote marks....and some of the others that I used here:
[pre]
%let t1=%str(Mary Poppins%nrstr(®) says %nrstr(")supercalifragilisticexpealidocious%nrstr(").);
%let t2=%nrstr(I love the chimney sweep%'s song Chim-Chim-Cheree);
%let t3=%str(zz%nrstr(ø)zzz%nrstr(©) zzzzz %nrstr(ß) zzzz zzzz %nrstr(ö) 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

Re: ODS ExcelXP Tagset & Very Long Titles

KCKC — Thu, 20 Jun 2013 15:38:32 GMT

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

Re: ODS ExcelXP Tagset & Very Long Titles

KCKC — Fri, 21 Jun 2013 04:07:05 GMT

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

^{style &t1...}