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

[Neeta]

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.

[Reeza]

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.

---