topic Re: How to change the column width with ods excel sas 9.4 in ODS and Base Reporting

How to change the column width with ods excel sas 9.4

zhangda — Tue, 06 Dec 2016 21:25:05 GMT

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;

Re: How to change the column width with ods excel sas 9.4

Reeza — Tue, 06 Dec 2016 22:00:43 GMT

Have you tried ABSOLUTE_COLUMN_WIDTH?

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

Re: How to change the column width with ods excel sas 9.4

zhangda — Wed, 07 Dec 2016 17:08:14 GMT

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!

Re: How to change the column width with ods excel sas 9.4

Reeza — Wed, 07 Dec 2016 18:13:10 GMT

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.

Re: How to change the column width with ods excel sas 9.4

Tim_SAS — Wed, 07 Dec 2016 19:55:09 GMT

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.

Re: How to change the column width with ods excel sas 9.4

zhangda — Wed, 07 Dec 2016 21:35:51 GMT

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!

Re: How to change the column width with ods excel sas 9.4

ballardw — Wed, 07 Dec 2016 21:51:41 GMT

@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.

Re: How to change the column width with ods excel sas 9.4

Reeza — Wed, 07 Dec 2016 22:27:24 GMT

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

Re: How to change the column width with ods excel sas 9.4

zhangda — Wed, 07 Dec 2016 23:18:49 GMT

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

Re: How to change the column width with ods excel sas 9.4

Tim_SAS — Thu, 08 Dec 2016 01:19:24 GMT

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.

Re: How to change the column width with ods excel sas 9.4

zhangda — Thu, 08 Dec 2016 01:57:22 GMT

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

Re: How to change the column width with ods excel sas 9.4

Reeza — Thu, 08 Dec 2016 02:08:34 GMT

@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.

Re: How to change the column width with ods excel sas 9.4

zhangda — Thu, 08 Dec 2016 14:11:35 GMT

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

Re: How to change the column width with ods excel sas 9.4

Tim_SAS — Thu, 08 Dec 2016 14:37:58 GMT

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

Re: How to change the column width with ods excel sas 9.4

zhangda — Thu, 08 Dec 2016 14:46:32 GMT

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.