topic Cannot WrapText and AUTOFIT_HEIGHT in excel in ODS and Base Reporting

Cannot WrapText and AUTOFIT_HEIGHT in excel

SanjayM — Wed, 20 Apr 2011 10:56:00 GMT

Hello,

This post is the same example as the previous posts but facing a different issue with not being able to wrap text in excel output.

ODS LISTING CLOSE;
ODS TAGSETS.EXCELXP FILE="&rep_filename."
STYLE=styles.printer
OPTIONS(EMBEDDED_TITLES='yes'
SHEET_INTERVAL = 'none'
WIDTH_FUDGE ='0.75'
SHEET_NAME = "&customer_number"
DEFAULT_COLUMN_WIDTH="10,0,8,8,8,8,8,8,8"
FITTOPAGE = 'yes'
PAGES_FITWIDTH = '1'
Pages_FitHeight = '100'
FROZEN_HEADERS = 'yes'
FROZEN_ROWHEADERS = 'yes'
GRIDLINES = 'no'
AUTOFIT_HEIGHT = 'yes'
PRINT_FOOTER = 'Page: &P of &N'
WRAPTEXT = 'yes'
)
;
ODS TAGSETS.EXCELXP
OPTIONS(EMBEDDED_TITLES='yes'
SHEET_INTERVAL='none'
SHEET_NAME="&customer_number"
ROW_REPEAT = '1 - 4'
FROZEN_HEADERS = '1 - 4'
);

title;
%*------ Export Main Report to EXCEL;
proc report data=sashelp.shoes split='*'
style(header) =[font_size=9pt font_weight=bold font_face=Arial borderwidth=1pt cellspacing=0pt cellpadding=1pt]
style(column) =[font_size=9pt font_face=Arial borderwidth=1pt cellspacing=0pt cellpadding=1pt]
;
columns region product;

define region / display "Region" style(column)={outputwidth=0.5in font_face=arial };
define product / display "Product" style(column)={outputwidth=0.5in font_face=arial };
run;
title;

%*------ CLOSE ODS TAGSETS.EXCELXP;
ODS TAGSETS.EXCELXP CLOSE;
ODS LISTING

Re: Cannot WrapText and AUTOFIT_HEIGHT in excel

Cynthia_sas — Wed, 20 Apr 2011 15:36:00 GMT

Hi:
When I run a version of your code, I get the following message in the log:
[pre]
Unrecognized option: WRAPTEXT
[/pre]

and when I use the doc='Help' suboption, I do not see WRAPTEXT as a valid suboption. So, I'm not sure what to suggest there.

However, when I run a version of your program (limited to 2 regions with long values that should wrap), I DO see wrapping and the height of the cell does autofit and expand.

My recommendation would be that you open a track with Tech Support on this issue.

I ran the code below in SAS 9.2 with TAGSETS.EXCELXP version: v1.94, 09/09/12

cynthia
[pre]
ODS LISTING CLOSE;
%let customer_number =111111;

data shoes;
set sashelp.shoes;
** select only some region values that should wrap;
where region in ('Western Europe', 'Central America/Caribbean');
output;
if _n_ = 1 then do;
region = 'An Extra Row';
product= 'To Repeat';
output;
output;
output;
end;
run;

proc sort data=shoes;
by region product;
run;

** added .XLS to file name, added doc='Help' to suboptions, removed duplicate suboptions and extra ODS statement;
ODS TAGSETS.EXCELXP FILE="c:\temp\wrap.xls"
STYLE=styles.printer
OPTIONS( doc='Help' EMBEDDED_TITLES='yes'
SHEET_INTERVAL = 'none'
WIDTH_FUDGE ='0.75'
SHEET_NAME = "&customer_number"
DEFAULT_COLUMN_WIDTH="10,0,8,8,8,8,8,8,8"
FITTOPAGE = 'yes'
PAGES_FITWIDTH = '1'
Pages_FitHeight = '100'
FROZEN_HEADERS = 'yes'
FROZEN_ROWHEADERS = 'yes'
GRIDLINES = 'no'
AUTOFIT_HEIGHT = 'yes'
PRINT_FOOTER = 'Page: &P of &N'
ROW_REPEAT = '1 - 4' FROZEN_HEADERS = '1 - 4'
WRAPTEXT = 'yes' ); /* this suboption is unrecognized */

title;
%*------ Create Main Report in a format that EXCEL can read and render;
proc report data=shoes split='*' nowd
style(header) =[font_size=9pt font_weight=bold font_face=Arial borderwidth=1pt
cellspacing=0pt cellpadding=1pt]
style(column) =[font_size=9pt font_face=Arial borderwidth=1pt
cellspacing=0pt cellpadding=1pt];
columns region product;

define region / display "Region" style(column)={outputwidth=0.5in font_face=arial };
define product / display "Product" style(column)={outputwidth=0.5in font_face=arial };
compute region;
if region = 'An Extra Row' then do;
call define(_row_,'style','style=Header');
end;
endcomp;
run;
title;

%*------ CLOSE ODS TAGSETS.EXCELXP;
ODS TAGSETS.EXCELXP CLOSE;
ODS LISTING ;
[/pre]

Re: Cannot WrapText and AUTOFIT_HEIGHT in excel

Andre — Thu, 21 Apr 2011 09:55:40 GMT

Hi,

Yesterday i was wondering too what was exactly the problem
as there is no guaranty of functionning in every cases

About wraptext it is by defaut 'yes' in version 1.116
with those remarks about both

Andre
[pre]

WrapText: Default Value 'yes'
Values: yes, no, on, off.
This value turns wraptext on and off for all style definitions.
This option should be used carefully. Wraptext is an attribute which is part of the style
definition, specifically, the alignment part. Turning this off will cause all style
definitions that are generated afterward, to leave out the wraptext setting. Turning it on
and off can work provided you understand that only styles generated from an over ride will
be affected by later changes to the setting.

This option also interacts with the tagattr wrap setting. If wrap is the opposite of the
setting given by this option then it is treated as a style over ride, and a new style is
generated with a new alignment tag, which also contain a redefinition of the vertical and
horizontal justifications. This interaction means that if you use these options together,
it is best to set WrapText at the very beginning and leave it alone. Otherwise the
interaction of the options and the styles already created could get complicated.

Autofit_height: Default Value 'no'
Values: yes, no, on, off.
If yes no row heights will be specified. This allows the auto fit height
of Excel to do it's job, sometimes not so well.
[/pre]