BookmarkSubscribeRSS Feed
zhangda
Fluorite | Level 6

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;

14 REPLIES 14
zhangda
Fluorite | Level 6

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
Super User

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
Barite | Level 11

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
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6

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

Reeza
Super User

@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
Super User

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

 

 

zhangda
Fluorite | Level 6

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

Tim_SAS
Barite | Level 11

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
Fluorite | Level 6
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
Barite | Level 11

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

 

excel.jpg

zhangda
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 15704 views
  • 0 likes
  • 4 in conversation