The SAS Output Delivery System and reporting techniques

How to change the column width with ods excel sas 9.4

Reply
Contributor
Posts: 74

How to change the column width with ods excel sas 9.4

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;

Super User
Posts: 19,034

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

Have you tried ABSOLUTE_COLUMN_WIDTH?

 

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

Contributor
Posts: 74

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

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!

Super User
Posts: 19,034

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

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. 

Super Contributor
Posts: 394

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

[ Edited ]

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.

 

Contributor
Posts: 74

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

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!

Super User
Posts: 11,105

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


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.

Contributor
Posts: 74

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

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

Super User
Posts: 19,034

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


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 Smiley Sad

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. 

 

 

 

Super User
Posts: 19,034

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

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

 

 

Contributor
Posts: 74

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

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

Super Contributor
Posts: 394

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

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.

Contributor
Posts: 74

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

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

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

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

 

excel.jpg

Contributor
Posts: 74

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

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.

Ask a Question
Discussion stats
  • 14 replies
  • 1348 views
  • 0 likes
  • 4 in conversation