SAS ODS Excel - Column width issue when multiple outputs on single sheet

Neeta — Tue, 17 Apr 2018 21:15:07 GMT

Hi,

I'm using ODS excel for printing multiple proc reports on single excel sheet. It appears that first table is deciding column width for all the tables. I'd like to customize column width for each table.

ods tagsets.excelxp file="C:\Users\XYZ\Desktop\try.xls" options(embedded_titles = 'yes' row_heights='20,35' width_points='1.5' sheet_interval = 'none' sheet_name = 'Class ');

title j=c bcolor=lightblue "Class - Age gender";

proc report data=sashelp.class headskip headline spacing=1 nofs split='|' missing
style(header)=[just=center ]
style(column)=[protectspecialchars=off asis=on just=center ] ;
column Name age sex;
define name / display style(column)=[cellwidth =200] "Subjid";
define age / display style(column)=[cellwidth =500] "age";
define sex / display style(column)=[cellwidth =200] "Sex";
run;

title j=c bcolor=lightblue "Class -Height Weight"; ;

proc report data=sashelp.class headskip headline spacing=1 nofs split='|' missing
style(header)=[just=center ]
style(column)=[protectspecialchars=off asis=on just=center ] ;
column Name height weight;
define name / display style(header)=[background=lightGray] style(column)=[cellwidth =200] "Subjid";
define height / display style(header)=[background=lightGray] style(column)=[cellwidth =200] "Height";
define weight / display style(header)=[background=lightGray] style(column)=[cellwidth =200] "Weight";

run;

ods tagsets.excelxp close;

Here, though I specified width of 200 for variable Height on second table but its taking width 500 from Age variable on first table.

Re: SAS ODS Excel - Column width issue when multiple outputs on single sheet

Reeza — Tue, 17 Apr 2018 22:18:58 GMT

That's sadly a limitation of Excel, a single column cannot have different widths so if happens to align it will take the widths from the table at the top.

The usual way to 'workaround' this issue is to merge/collapse cells, but TAGSETS does not support that.

@Neeta wrote:

Hi,

I'm using ODS excel for printing multiple proc reports on single excel sheet. It appears that first table is deciding column width for all the tables. I'd like to customize column width for each table.

ods tagsets.excelxp file="C:\Users\XYZ\Desktop\try.xls" options(embedded_titles = 'yes' row_heights='20,35' width_points='1.5' sheet_interval = 'none' sheet_name = 'Class ');

title j=c bcolor=lightblue "Class - Age gender";

proc report data=sashelp.class headskip headline spacing=1 nofs split='|' missing
style(header)=[just=center ]
style(column)=[protectspecialchars=off asis=on just=center ] ;
column Name age sex;
define name / display style(column)=[cellwidth =200] "Subjid";
define age / display style(column)=[cellwidth =500] "age";
define sex / display style(column)=[cellwidth =200] "Sex";
run;

title j=c bcolor=lightblue "Class -Height Weight"; ;

proc report data=sashelp.class headskip headline spacing=1 nofs split='|' missing
style(header)=[just=center ]
style(column)=[protectspecialchars=off asis=on just=center ] ;
column Name height weight;
define name / display style(header)=[background=lightGray] style(column)=[cellwidth =200] "Subjid";
define height / display style(header)=[background=lightGray] style(column)=[cellwidth =200] "Height";
define weight / display style(header)=[background=lightGray] style(column)=[cellwidth =200] "Weight";

run;

ods tagsets.excelxp close;

Here, though I specified width of 200 for variable Height on second table but its taking width 500 from Age variable on first table.

topic SAS ODS Excel - Column width issue when multiple outputs on single sheet in ODS and Base Reporting

SAS ODS Excel - Column width issue when multiple outputs on single sheet

Re: SAS ODS Excel - Column width issue when multiple outputs on single sheet