topic Re: cellwidth and ExcelXP in ODS and Base Reporting

cellwidth and ExcelXP

steve_citi — Thu, 08 Oct 2009 14:28:56 GMT

Can anyone explain why the cellwidth style option in this example works for the variable Area_Name, but NOT for High_Level_Definition

PROC TABULATE data=calif;
CLASS Area_Name / order=freq;
CLASSLEV Area_Name / style=[cellwidth=230];
CLASS High_Level_Definition / order=freq;
CLASSLEV High_Level_Definition / style=[cellwidth=100];
Table (Area_Name ALL='Decision Total'*[style=]),(ALL='Area Total'*[style={background=vpapb}] High_Level_Definition=" ")*
(N=' ' *f=comma6.)
/ BOX={label='Area by Reasons' style=[cellwidth=230]} NOCONTINUED indent=3 ;
KEYWORD ALL / style=[background=vpapb];
run;

Re: cellwidth and ExcelXP

Cynthia_sas — Thu, 08 Oct 2009 15:06:51 GMT

Hi:
Excel may not always honor the cellwidths you set inside SAS. That is why TAGSETS.EXCELXP has 2 different sub-options for specifying a cellwidth for Excel to use when it opens and renders the XML in the ExcelXP result file:
default_column_width
absolute_column_width

If you alter your ODS TAGSETS.EXCELXP invocation to use the Help facility:
[pre]
ods tagsets.excelxp file='tabxp.xml' style=sasweb;
options(doc='Help');
[/pre]

You will find the list of complete options in the SAS Log.

In addition, this paper about creating "printable" spreadsheets with TAGSETS.EXCELXP may be useful.
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf (see page 10 for an example of absolute column width usage)

cynthia

Re: cellwidth and ExcelXP

steve_citi — Thu, 08 Oct 2009 16:36:08 GMT

SAS did honor the cellwidth for Area_Name. Can the other 2 alternatives you mention adjust the width of only a specific variable, or does it adjust all columns in the same way.

Re: cellwidth and ExcelXP

Cynthia_sas — Thu, 08 Oct 2009 16:40:31 GMT

Hi:
When SAS creates the file, SAS does not "honor" the cellwidth. SAS merely places the instruction into the TAGSETS.EXCELXP file. Then it is Excel which RENDERS the file. So it is EXCEL that is honoring the cellwidth for Area_Name, but not honoring the cellwidth for the other columns -- this happens when the file is RENDERED (by EXCEL) not when the file is CREATED (by SAS).

cynthia

Re: cellwidth and ExcelXP

steve_citi — Thu, 08 Oct 2009 16:54:20 GMT

Okay....are we sure SAS is placing the instruction into the code which Excel reads to RENDER the spreadsheet.

Someone is dropping the ball on this one.

Re: cellwidth and ExcelXP

Cynthia_sas — Thu, 08 Oct 2009 18:22:45 GMT

Hi:
When I run a test (using data from SASHELP.PRDSALE), but using your TABULATE statements and cellwidths, this is what I see when Iook at the Spreadsheet Markup Language XML using Notepad:
[pre]
<Table ss:StyleID="_body">
<ss:Column ss:AutoFitWidth="1" ss:Width="165.6"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="45"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="72"/>
<ss:Column ss:AutoFitWidth="1" ss:Width="72"/>
<Row ss:AutoFitHeight="1" ss:Height="28">
<Cell ss:StyleID="header__c" ss:Index="1"><Data ss:Type="String">Area by Reasons</Data></Cell>
<Cell ss:StyleID="header__c1" ss:Index="2"><Data ss:Type="String">Area Total</Data></Cell>
<Cell ss:StyleID="header__c" ss:Index="3"><Data ss:Type="String">EAST</Data></Cell>
<Cell ss:StyleID="header__c" ss:Index="4"><Data ss:Type="String">WEST</Data></Cell>
</Row>
....more Spreadsheet ML XML ....
</Table>
</Worksheet>
</Workbook>
[/pre]

ODS must convert the SAS cellwidths to the correct unit of measure for the XML specification that conforms to the Microsoft specification for Spreadsheet Markup Language XML. I note that AutoFitWidth XML attribute is set to "1" (which generally means ON) and that the size for the first column (Area by Reasons) is larger than the size for the other columns. This is Microsoft XML. ODS is writing what it considers to be the equivalent of your cellwidths. If you don't like how Microsoft Excel renders that XML, then either change the cellwidth until it looks closer to what you want or use the sub-options.

However, I have seen Excel just totally ignore any cellwidth that I specify. That's why the ABSOLUTE_COLUMN_WIDTH sub-option was created.

cynthia

Re: cellwidth and ExcelXP

steve_citi — Thu, 08 Oct 2009 18:35:25 GMT

Cynthia, changing the cell width until it looks what what i need would be a simple enough solution, IF excel was responding to what I am setting it to. But, my column width for this field is always 48 pixels, regardless of what i specify.

Will specifying the option absolute_column_width change all of my columns? Will it use the header to determine column width? How exactly does it work?

Re: cellwidth and ExcelXP

Cynthia_sas — Thu, 08 Oct 2009 21:23:33 GMT

Hi:
The times I have used the Absolute_Column_Width sub-option I have explicitly changed all my columns. For example, if I do this:
[pre]
data calif;
set sashelp.prdsale;
Area_Name= Country;
High_Level_Definition = Region;
run;

ods tagsets.excelxp file='calif2.xml' style=sasweb
options(absolute_column_width="30,10,7,7");

PROC TABULATE data=calif;
CLASS Area_Name / order=freq;
CLASSLEV Area_Name ;
CLASS High_Level_Definition / order=freq;
CLASSLEV High_Level_Definition ;
Table (Area_Name ALL='Decision Total'*[style=]),
(ALL='Area Total'*[style={background=vpapb}] High_Level_Definition=" ")*(N=' ' *f=comma6.)
/ BOX={label='Area by Reasons'} NOCONTINUED indent=3 ;
KEYWORD ALL / style=[background=vpapb];
run;
ods _all_ close;
[/pre]

Then for my output, using SASHELP.PRDSALE, I see that the first column is significantly wider than the last 2 columns.

I am not exactly sure how column width works. In Excel, if I look at informatiion about column width settings, Excel says that (from the Excel help):

On a worksheet, you can specify a column width of 0 (zero) to 255. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font (standard font: The default text font for worksheets. The standard font determines the default font for the Normal cell style.). The default column width is 8.43 characters. If a column has a width of 0 (zero), the column is hidden.

Then, on this Microsoft site, there's more information about column width:
http://support.microsoft.com/kb/214123

I don't exactly know how ODS deals with the numbers you send via the Absolute_column_width sub-option -- I surmise (but don't know for sure) that somehow the cell font is taken into account by ODS along with the absolute column width and it is some changed value that is being sent to Excel in XML form.

Personally, I don't stress over what ODS is doing. I take the "control freak" approach: and specify absolute column widths for EVERY column and change the numbers I send to ODS until what I see in Excel is what I want.

If it is essential for you to understand -exactly- how ODS is dealing with the absolute_column_width sub-option, then your best resource is to contact Tech Support. I only know that when I use the absolute_column_width sub-option with TAGSETS.EXCELXP, I can always get Excel to render the columns showing a width that I am happy with.

cynthia