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?
@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.
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.
@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.
What are you using for the value of borderleftwidth? (and borderrightwidth)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.