How to change the column width with ods excel sas 9.4

[zhangda]

Hi, 

I am coding ods excel file with sas 9.4, would you please guide me how to change the column width? Please see the code below. Thanks!

ODS excel file ="/opt/sas/Report120616_38679.xlsx" ;

ods excel options( embedded_titles='on' embedded_footnotes='yes' sheet_name="Metrics " sheet_interval="none");

Proc report data=all  contents=" " nowd split='\'
/*style(report)={font_size=14pt cellpadding=4pt cellspacing=1 rules=none frame=void}*/
style(header)={color=black background=cornflowerblue fontweight=bold }
style(column)={just=center background=snow fontweight=bold };
title1 j=c color=black HEIGHT=14pt BOLD "All Products";
footnote1 j=c COLOR=BLACK BOLD "***Data as of &yesterday.***";
footnote2 j=c COLOR=BLACK BOLD "*** The date is from &cur_apr_b. to current***";
column _name_ year2016 year2015 Num_of_Variance mature2015 Num_of_Variance2 y2016 y2015 Variance ;

define _name_ / "Mets" style=[ fontstyle=Roman fontweight=bold font_size=2.2 cellwidth=0.5in just=center ];
define year2016/ "&Mar_Yr." style=[cellwidth=0.2in];
define year2015/ " &Mar_PreYr." style=[cellwidth=0.2in];
define Num_of_Variance/ "# of Variance" style=[foreground=negfmt.];
define m2015/ "Ma &Mar_PreYr." style=[cellwidth=0.2in];
define Num_of_Variance2/ "# of Variance" style=[foreground=negfmt.];
define y2016/ "YTD16" style=[foreground=negfmt.];
define y2015/ " YTD15" style=[foreground=negfmt.];
define Variance/ "# of Variance" style=[foreground=negfmt.];

compute _name_ ;
if _name_= 'Avg ' then call define(_row_,'style','style={background=gainsboro}');
if _name_= 'Avg App' then call define(_row_,'style','style={background=gainsboro}');

endcomp;

%macro comp(arg);
compute &arg;
If _name_='App' then call define(_col_,'format','comma20.0');
If _name_='CAR' then call define(_col_,'style',"style=[tagattr='format:0.0%']");
if _name_= 'JAR' then call define(_col_,'style',"style=[tagattr='format:0.0%']");
if _name_= 'R % ' then call define(_col_,'style',"style=[tagattr='format:0.0%']");
if _name_= 'Avg ' then call define(_col_,'format','comma8.0');
if _name_= 'AvgA' then call define(_col_,'format','comma8.0');

endcomp;
%mend common_lines;

%comp(year2015);
%comp(year2016);
%comp( Num_of_Variance);
%comp(mature2015);
%comp( Num_of_Variance2);
%comp( y2016);
%comp(y2015);

run;

ods EXCEL close;

[Reeza]

Have you tried ABSOLUTE_COLUMN_WIDTH?

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

[zhangda]

Hi Reeza,

thank you so much for your suggestion, but what if I have a couple of worksheet for one excel workbook, and for each worksheet, the tables have a couple of columns whose cellwidth varies. Can the absolute_column_width be customized for the tabels of each worksheet? Thanks!

[Reeza]

Yes, you can specify an option statement before each PROC. 

Ods excel option (...) ;

proc tabulate code....

ods excel option(...); 

proc report code(....);

ods excek close; 

I'll move this to the reporting forum to see if you get a better answer than mine. 

[Tim_SAS]

You can use the ABSOLUTE_COLUMN_WIDTH option to change the column widths on each separate worksheet but (to be absolutely obvious), because there is only one set of columns on a worksheet, if you have more than one table on a worksheet then all the tables must have the same column widths.

[zhangda]

Hi Tim,

Yes, you guess right. What if I have more than one table on a worksheet, and I dont want the same column widths for all the tables on my worksheet. Thank you so much!

[ballardw]

---

@zhangda wrote:

Hi Tim,

Yes, you guess right. What if I have more than one table on a worksheet, and I dont want the same column widths for all the tables on my worksheet. Thank you so much!

---

One option only: No spreadsheet. Move to HTML, PDF or RTF output. I have not seen any spreadsheets that allows separate column widths in different locations on a single sheet. The closest is merged cells but the left and right boundaries will still align with other cells.

So decide which is more important: Different column widths or Spreadsheet output.

[zhangda]

Merged cells are good for me, do you have any code samples? Thanks!

[Reeza]

---

@zhangda wrote:

Merged cells are good for me, do you have any code samples? Thanks!

---

None that I'm aware of to do it automatically 😞

It has to happen manually. If you're mostly interested in appearance, consider using HTML and then saving the file to an Excel format afterwards. 

[Reeza]

The limitation here is Excel, not SAS. Columns have a simgle width. 

[zhangda]

Is there any options that could merge the two columns width into one column ?

[Tim_SAS]

I agree with @ballardw. When you choose Excel as your reporting format then you accept its limitations, and one of its limitations is that you only get one set of columns per worksheet. ODS doesn't have any magic bullet to change that. If you must use Excel then accept it. If you must have multiple tables with different column widths per "page," then use a different report format.

[zhangda]

Hi Tim,
Do you have any idea shrinking the tables of one worksheet into one page to print out for ods excel? Thanks!

[Tim_SAS]

Excel will do that for you. In Excel 2013, choose Print, then select Fit Sheet on One Page. Here's a picture.

[zhangda]

Hi Tim,

Thank you so much for it! I am kind of all-with-sas-code now, actually there is easier way without having sas coding involved.