DATA Step, Macro, Functions and more

Title truncated on export to Excel

Reply
Occasional Contributor
Posts: 8

Title truncated on export to Excel

I use the 'title_foot_note_width' option when exporting to get my Title to span multiple columns, this however does not seem to work and my title appears truncated and only displayed in the first narrow cell of the output sheet. Here is my code;

 

Options nocenter;
title1 bold height=14pt color=red "NOT VALIDATED - FOR INTERNAL USE ONLY - NOT VALIDATED - FOR INTERNAL USE ONLY - NOT VALIDATED - FOR INTERNAL USE ONLY";
ods listing close;
ods noresults;
ods tagsets.ExcelXP style=seaside file="&output.\&OUTFILE."
options(sheet_name="test"
Embedded_titles = 'Yes'
autofilter='all'
frozen_headers='3'
title_footnote_width='5');
Proc print
data=everything noobs label
style(Header)=[background=#009FDA font_size=10pt just=center] ;
Run;

ods tagsets.ExcelXP close;
ods listing ;
title1;

 

Please find screen dump of truncated title below;truncated_title.jpg

Super User
Super User
Posts: 9,840

Re: Title truncated on export to Excel

[ Edited ]

Its likely to be an Excel "feature".  When I run it the text is centered and so I see this:

Capture.PNG

 

Now, one way to fix it is to make the cells big enough to show the whole title, in my instance I updated my code to include (in proc report not print) the style= to make all the columns 5cm in width:

ods tagsets.excelxp file="s:/temp/rob/test.xml" options(sheet_name="test" embedded_titles = 'Yes' autofilter='all'
                                                        frozen_headers='3')  style=statistical;
title1 j=l bold height=14pt color=red "NOT VALIDATED - FOR INTERNAL USE ONLY - NOT VALIDATED - FOR INTERNAL USE ONLY - NOT VALIDATED - FOR INTERNAL USE ONLY";
proc report data=sashelp.class nowd style(column)=[cellwidth=5cm];
run;
ods tagsets.excelxp close;

 

If you have SAS 9.3 onwards you can use ods excel, which does seem to make this work correctly and wrap accordingly:

ods excel file="s:/temp/rob/test.xlsx" options(sheet_name="test" embedded_titles = 'Yes' autofilter='all'
                                               frozen_headers='3' title_footnote_width='5')  style=statistical;
title1 bold height=14pt color=red "NOT VALIDATED - FOR INTERNAL USE ONLY - NOT VALIDATED - FOR INTERNAL USE ONLY - NOT VALIDATED - FOR INTERNAL USE ONLY";
proc report data=sashelp.class nowd style(column)=[cellwidth=5cm];
run;
ods excel close;

 

Alternatively you could put the title in the titles section and change the view to print layout.  To be honest though, Excel is a spreadsheet, titles/footnotes are document items.

 

Edit: Note, if your still only still one word, you may need to put j=l after title1, e.g. title1 j=1 bold...

Otherwise it gets centered.

Ask a Question
Discussion stats
  • 1 reply
  • 158 views
  • 0 likes
  • 2 in conversation