PROC Tabulate and EXCELXP - using cellheight on headers

deleted_user — Fri, 10 Jul 2009 19:43:27 GMT

I am using PROC TABULATE and EXCELXP to create a table in Excel. Problem is, the column headings are long and the cell height doesn't expand to make the cell taller to fit the text. Have tried putting CELLHEIGHT in various places with no success.

I am using: Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08).

Here's the code:

ods tagsets.ExcelXP file=Outfile options(AUTOFIT_HEIGHT='YES')
style=styles.mytab styles.Statistical ;

proc format;
value $division
'CONSUMER'="this is very long text for consumer"
'EDUCATION'="this is very long text for education";
run;

proc tabulate data=sashelp.prdsale;
format division $division.;
label division='this is a very long label for division. the cellheight needs to increase so that the value will fit';
class country region division ;
classlev country region division/style=[cellwidth=200px];

table (country),division*(n pctn)

;run;

Re: PROC Tabulate and EXCELXP - using cellheight on headers

Cynthia_sas — Fri, 10 Jul 2009 21:42:51 GMT

Hi:
When I try your code in SAS 9.2, with AUTOFIT, I see that Excel has just widened the table instead of wrapping the text. So, my long text was taken care of by the cell -width- being increased by Excel but I -do- see all the text in all the headers.

In the SAS Log, when I use the Doc='Help' suboption, it says this about the AUTOFIT_HEIGHT suboption:
[pre]
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]

It seems that you are experiencing the "sometimes not so well" behavior. Perhaps Tech Support will know what combination of sub-options will cause the cell height in Excel to increase (and the text to wrap) as you want.

cynthia

topic PROC Tabulate and EXCELXP - using cellheight on headers in ODS and Base Reporting

PROC Tabulate and EXCELXP - using cellheight on headers

Re: PROC Tabulate and EXCELXP - using cellheight on headers