topic Re: How to reduce column width in Excel in SAS Programming

How to reduce column width in Excel

shlomiohana — Mon, 14 Apr 2025 10:02:51 GMT

Hello, I am creating an Excel file using the following code.
The table in Excel starts from column B.
I want to minimize the width of the empty column A in Excel.
How do I do this?

The code:
%macro create_report(area=,sheet=);
ods excel options (sheet_interval="proc" sheet_name="&sheet." start_at="B1" embedded_titles='yes');
title justify=center bold height=12pt "REPORT TEST";
proc report data= work.ATM_LEUMI_HIZUI_P nowd
style(column)={just=right font_face="Arial" font_size=10pt background=white}
style(header)={background=cxD9E1F2 foreground=cx0F2B50 font_weight=bold font_size=10pt just=center};
column
'AREA'n
'BRANCH'n;
define 'AREA'n /display;
define 'BRANCH'n /display style(column)={cellwidth=110pt};
%if &area. ne ALL %then %do; where 'AREA'n="&area."; %end;
RUN;
%mend create_report;

ods excel file="/cifs/accounts/ATM_LEUMI_HIZUI.xlsx";
%create_report(area=ALL,sheet=HIZUY);
%create_report(area=NORTH,sheet=NORTH);
%create_report(area=SOUTH,sheet=SOUTH);
ods excel close;

Thanks.

Re: How to reduce column width in Excel

ballardw — Mon, 14 Apr 2025 10:19:50 GMT

(ABSOLUTE_COLUMN_WIDTH ='number-list '| 'NONE') 


specifies the columnwidths. Lists widths to use for columns instead of allowing SAS todetermine the column width (measured widths). The number-list isa comma separated list of numbers. You can use 'NONE' toreset to the default. 

Default None. SAS determines the width. 
Example ods excel file='footer.xlsx'    
          options(absolute_column_width='16');
proc print data=sashelp.class(obs=5);
run;
ods excel close;

From the online help on ODS EXCEL. This sets the first column to 16. If you want to specify additional widths separate the values by columns in a list following absolute_column_width= such as ('1,12,45').

Columns to the right of the number specified are treated as default determined by SAS.

Re: How to reduce column width in Excel

Ksharp — Tue, 15 Apr 2025 03:46:46 GMT

As @ballardw showed you :

 ods excel file='c:\temp\footer.xlsx'    
          options(absolute_column_width='1,12,12,12,12,12,12' start_at="B1" embedded_titles='yes');
		  title justify=center bold height=12pt "REPORT TEST";
proc report data=sashelp.class(obs=5) nowd;
run;
ods excel close;