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:
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: