BookmarkSubscribeRSS Feed
KimLeBouton
Quartz | Level 8
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;
8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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(&#10;)&t2 %str(&#10;)&t3 }";
proc print data=sashelp.class
style(column)={cellwidth=1.75in};
run;
ods _all_ close;
[/pre]
KimLeBouton
Quartz | Level 8
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
KimLeBouton
Quartz | Level 8
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
Cynthia_sas
SAS Super FREQ
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
KCKC
Calcite | Level 5

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

KCKC
Calcite | Level 5

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

^{style &t1...}

KimLeBouton
Quartz | Level 8
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
Cynthia_sas
SAS Super FREQ
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(&reg;) says %nrstr(&quot;)supercalifragilisticexpealidocious%nrstr(&quot;).);
%let t2=%nrstr(I love the chimney sweep%'s song Chim-Chim-Cheree);
%let t3=%str(zz%nrstr(&oslash;)zzz%nrstr(&copy;) zzzzz %nrstr(&szlig;) zzzz zzzz %nrstr(&ouml;) 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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4516 views
  • 0 likes
  • 3 in conversation