I want to create table with Row and Column Spanning. i use Report Writing Interface and Proc report .
data have;
input A B $ C E F G I K L M N;
datalines;
1 japan 190 46 15 0 0 0 0 0 1
2 us 152 39 47 86 0 0 0 0 1
3 aus 50 6 36 41 0 0 0 0 1
;
data have1;
input A B $ C E F G I K L M N;
datalines;
1 ind 10 4 1 0 0 0 0 0 1
2 nep 12 3 7 8 0 0 0 0 1
3 newz 5 6 6 1 0 0 0 0 1
;
ods excel file="report.xlsx" options (sheet_interval="none") ;
data _null_;
set have end=done;
* first start the table and create the header;
if _n_ eq 1 then do;
declare odsout t(); * create a report writing interface object named t;
t.table_start(); * start the table;
t.head_start(); * start the header (so that these items get the default header style);
* in this case the header is 3 rows in height. the ROWSPAN and COLSPAN items
control the size of each cell in the header;
t.row_start();
t.format_cell(text: 'A', rowspan:3);
t.format_cell(text: 'B', rowspan:3);
t.format_cell(text: 'C', rowspan:3);
t.format_cell(text: 'D', rowspan:2, colspan:2);
t.format_cell(text: 'G', rowspan:3);
t.format_cell(text: 'H', colspan:5);
t.row_end();
t.row_start();
t.format_cell(text: 'I', rowspan:2);
t.format_cell(text: 'J', colspan:3);
t.format_cell(text: 'N', rowspan:2);
t.row_end();
t.row_start();
t.format_cell(text:'E');
t.format_cell(text:'F');
t.format_cell(text:'K');
t.format_cell(text:'L');
t.format_cell(text:'M');
t.row_end();
t.head_end();
end;
* the rest of the cells are all simply data values;
t.row_start();
t.format_cell(text: A);
t.format_cell(text: B);
t.format_cell(text: C);
t.format_cell(text: E);
t.format_cell(text: F);
t.format_cell(text: G);
t.format_cell(text: I);
t.format_cell(text: K);
t.format_cell(text: L);
t.format_cell(text: M);
t.format_cell(text: N);
t.row_end();
if done then t.table_end();
run;
proc report data=have1 ;
column ("A" ("" A)) ("B" ("" B)) ("C" ("" C)) ("D" (("E" E) ("F" F))) ("G" ("" G))
("H" ("I" I) ('J' K L M) ("N" N));
define A / "" display;
define B / "" display;
define C / "" display;
define E / "" display;
define F / "" display;
define G / "" display;
define I / "" display;
define K / display;
define L / display;
define M / display;
define N / "" display;
run;
ods _all_ close;
i have
i want
The DATA _NULL_ technique produced the have report in your desired format.
You can use the ODS EXCEL statement sheet_interval="table" option to make the tables appear on individual tabs.
data have;
input A B $ C E F G I K L M N;
datalines;
1 japan 190 46 15 0 0 0 0 0 1
2 us 152 39 47 86 0 0 0 0 1
3 aus 50 6 36 41 0 0 0 0 1
;
data have1;
input A B $ C E F G I K L M N;
datalines;
1 ind 10 4 1 0 0 0 0 0 1
2 nep 12 3 7 8 0 0 0 0 1
3 newz 5 6 6 1 0 0 0 0 1
;
ods excel file="report.xlsx" options (sheet_interval="table") ;
data _null_;
set have end=done;
* first start the table and create the header;
if _n_ eq 1 then do;
declare odsout t(); * create a report writing interface object named t;
t.table_start(); * start the table;
t.head_start(); * start the header (so that these items get the default header style);
* in this case the header is 3 rows in height. the ROWSPAN and COLSPAN items
control the size of each cell in the header;
t.row_start();
t.format_cell(text: 'A', rowspan:3);
t.format_cell(text: 'B', rowspan:3);
t.format_cell(text: 'C', rowspan:3);
t.format_cell(text: 'D', rowspan:2, colspan:2);
t.format_cell(text: 'G', rowspan:3);
t.format_cell(text: 'H', colspan:5);
t.row_end();
t.row_start();
t.format_cell(text: 'I', rowspan:2);
t.format_cell(text: 'J', colspan:3);
t.format_cell(text: 'N', rowspan:2);
t.row_end();
t.row_start();
t.format_cell(text:'E');
t.format_cell(text:'F');
t.format_cell(text:'K');
t.format_cell(text:'L');
t.format_cell(text:'M');
t.row_end();
t.head_end();
end;
* the rest of the cells are all simply data values;
t.row_start();
t.format_cell(text: A);
t.format_cell(text: B);
t.format_cell(text: C);
t.format_cell(text: E);
t.format_cell(text: F);
t.format_cell(text: G);
t.format_cell(text: I);
t.format_cell(text: K);
t.format_cell(text: L);
t.format_cell(text: M);
t.format_cell(text: N);
t.row_end();
if done then t.table_end();
run;
data _null_;
set have1 end=done;
* first start the table and create the header;
if _n_ eq 1 then do;
declare odsout t(); * create a report writing interface object named t;
t.table_start(); * start the table;
t.head_start(); * start the header (so that these items get the default header style);
* in this case the header is 3 rows in height. the ROWSPAN and COLSPAN items
control the size of each cell in the header;
t.row_start();
t.format_cell(text: 'A', rowspan:3);
t.format_cell(text: 'B', rowspan:3);
t.format_cell(text: 'C', rowspan:3);
t.format_cell(text: 'D', rowspan:2, colspan:2);
t.format_cell(text: 'G', rowspan:3);
t.format_cell(text: 'H', colspan:5);
t.row_end();
t.row_start();
t.format_cell(text: 'I', rowspan:2);
t.format_cell(text: 'J', colspan:3);
t.format_cell(text: 'N', rowspan:2);
t.row_end();
t.row_start();
t.format_cell(text:'E');
t.format_cell(text:'F');
t.format_cell(text:'K');
t.format_cell(text:'L');
t.format_cell(text:'M');
t.row_end();
t.head_end();
end;
* the rest of the cells are all simply data values;
t.row_start();
t.format_cell(text: A);
t.format_cell(text: B);
t.format_cell(text: C);
t.format_cell(text: E);
t.format_cell(text: F);
t.format_cell(text: G);
t.format_cell(text: I);
t.format_cell(text: K);
t.format_cell(text: L);
t.format_cell(text: M);
t.format_cell(text: N);
t.row_end();
if done then t.table_end();
run;
ods excel close;
Results:
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.