Creating a Table with Row and Column Spanning

Daily1 — Fri, 12 Jan 2024 09:02:32 GMT

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

Re: Creating a Table with Row and Column Spanning

SASJedi — Fri, 12 Jan 2024 13:23:24 GMT

The DATA _NULL_ technique produced the have report in your desired format.

Why did you use PROC REPORT instead of a second DATA _NULL_ step?
Do you want the second table to appear in its own tab? It kind of looks that way from how you posed the question.

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:

topic Creating a Table with Row and Column Spanning in SAS Enterprise Guide

Creating a Table with Row and Column Spanning

Re: Creating a Table with Row and Column Spanning