topic Re: columns' width is changed when exporting to excel (using proc report and ods excel) in ODS and Base Reporting

columns' width is changed when exporting to excel (using proc report and ods excel)

Rabelais — Thu, 01 Aug 2024 09:19:26 GMT

I'm trying to set a specific width to each column in my report and I expect to find the same widths in the excel file created by ods excel, but when I open the file in excel the widths are changed (bigger)... why does this happen?

Let's see an example

data test;
very_long_variable_name=123;
description="lorem ipsum dolor sit amet consectetur adipiscing elit sed do eiusmod tempor incididunt";
status="pass";
output;
run;

proc template;
define style styles.excel_update;
parent=styles.excel;
class header, body, data / fontfamily=Calibri fontsize=11pt color=black fontweight=medium backgroundcolor=_undef_ just=left;
end;
run;

ods excel
	file="your_path\test.xlsx"
	style=styles.excel_update 
	options(flow="tables" absolute_row_height="20px");

proc report
	data=test;
	columns _ALL_;
	define very_long_variable_name / style(column)={width=1.86in};
	define description	/ style(column)={width=6.26in};
	define status 		/ style(column)={width=0.48in};
run;

ods excel close;

To choose the width values 1.86in, 6.26in and 0.48in I opened the excel file and used the "auto fit width" feature. Then by using the "Page Layout" view I can see the exact width inches of each column, as shown in the image below.

The following is a screenshot from the xlsx created by the previous code. As you can see the columns' width are larger.

for example the first column has a width of 2.35in, even if I set it to be equal to 1.86in (the other columns are 7.80in and 0.67in)

Why the columns' width are changed? Is there a way in the SAS code to set the column' widths so that the excel file will have the same widths?

Re: columns' width is changed when exporting to excel (using proc report and ods excel)

ballardw — Thu, 01 Aug 2024 14:16:17 GMT

Use the absolute_column_width options instead of the Report procedure if you really need to control specific column widths. Any output sent to the sheet prior to Proc Report will set the column widths because of the way Excel does things. You can't have a column change widths (any apparent exception to this usually will have merged cells involved).

Also providing a measurement in Pixels is somewhat problematic as there is no standard size for a "pixel", they are display dependent and one systems 20 pixels may be minuscule or gigantic on a different device. I would recommend using IN, CM or MM for units as those are human understandable in code and should work across devices.

Re: columns' width is changed when exporting to excel (using proc report and ods excel)

Rabelais — Thu, 01 Aug 2024 15:15:07 GMT

Thanks, but absolute_column_width does set the same width to all columns, doesn't it?

What if I need to set a different width to each column?

Re: columns' width is changed when exporting to excel (using proc report and ods excel)

ballardw — Thu, 01 Aug 2024 17:04:07 GMT

@Rabelais wrote:
Thanks, but absolute_column_width does set the same width to all columns, doesn't it?

What if I need to set a different width to each column?

see what happens to your output when you add absolute_column_width='0.5in,2.4in,3.1in'

You can provide a list of values. The first is for column 1 or A, second for column 2 or B, etc. . Then the settings repeat if columns to the right are used for columns past the defined. Column D would be the same width as A, E as B, F as C.

If you provide a single value then all columns are the same. Just a degenerate behavior of the repeating column definitions above.

Re: columns' width is changed when exporting to excel (using proc report and ods excel)

Tom — Thu, 01 Aug 2024 18:25:38 GMT

What are you using for the value of borderleftwidth? (and borderrightwidth)

Re: columns' width is changed when exporting to excel (using proc report and ods excel)

Rabelais — Fri, 02 Aug 2024 15:25:55 GMT

@ballardw It works! Didn't know we can pass a list of widths, many thanks!

Re: columns' width is changed when exporting to excel (using proc report and ods excel)

Rabelais — Fri, 02 Aug 2024 15:26:43 GMT

@Tom I did not set any value for borderleftwidth, so I guess SAS uses the default one