sasproc-report

Merge cells horizontally in RTF output using proc report


I am trying to create a summary row above each group in my data. I have 2 questions:

  1. How do I merge the first 2 cells horizontally (the ones in red below) in the summary rows.
  2. How do I remove the duplicated F and M in the Sex column (at the moment I can work around this by changing only those cell's text colours to white, but hopefully there's a better way)

The output is an RTF file, and I'm using SAS 9.4 - the desktop version.

Is this possible using proc report?

Code:

options missing=' ';
proc report data=sashelp.class nowd;
    columns sex name age weight;
    define sex / order;
    break before sex / summarize;
run;

enter image description here


Solution

  • I don't think you can merge cells in the summarize line.

    Some trickery with compute blocks and call define can alter the cell values and appearances.

    For example (Just J names for smaller image):

    proc report data=sashelp.class nowd;
        where name =: 'J';
    
        columns sex name age weight;
    
        define sex / order;
        define age / sum;
        define weight / sum;
        break before sex / summarize style=[verticalalign=bottom];
    
        compute name;
          * the specification of / order for sex sets up conditions in the name value
          * that can be leveraged in the compute block;
    
          if name = ' ' then do; 
            * a blank name means the current row the compute is acting on 
            * is the summarization row;
    
            * uncomment if stat is not obvious or stated in title;
            * name = 'SUM';  
    
            * 'hide' border for appearance of merged cell;
            call define (1, 'style', 'style=[fontsize=18pt borderrightcolor=white]');
          end;
          else do;
            * a non-blank name means one of the detail rows is being processed;
            * blank out the value in the sex column of the detail rows;
            * the value assignment can only be applied to current column or those
            * to the left;
            sex = ' ';
          end;
        endcomp;
    
        compute after sex;
          * if you want more visual separation add a blank line;
          * line ' ';
        endcomp;
    run;
    

    enter image description here