BookmarkSubscribeRSS Feed
Daily1
Quartz | Level 8

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 

Daily1_0-1705049416099.png

i want 

Daily1_1-1705049458966.png

 

 

1 REPLY 1
SASJedi
SAS Super FREQ

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

  1. Why did you use PROC REPORT instead of a second DATA _NULL_ step? 
  2. 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:

 

SASJedi_2-1705065704757.png

 

SASJedi_1-1705065670456.png

 

Check out my Jedi SAS Tricks for SAS Users

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

SAS Enterprise Guide vs. SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 581 views
  • 2 likes
  • 2 in conversation