Blogs

Blogs

What's so great about SPANROWS?

15
By

A customer just recently posted this on our Facebook page: “Dear SAS: Thank you for SPANROWS!” That question inspired me to write a quick blog that is not going to refer to Herman Melville or James Joyce in regard to learning SAS! I will return to those blog topics soon. But in the meantime . . . read on!

You may have wondered just what the SPANROWS option was used for and why we were being thanked for the SPANROWS option. Well, wonder no more.

The SPANROWS option is a new feature of PROC REPORT (new in SAS 9.2). But this is a “chicken and egg” explanation. In order to understand how wonderful SPANROWS is, you need to understand a basic difference between PROC REPORT and PROC TABULATE. Compare the two outputs below:

Example of Default Row Spanning Behavior
Notice how the PROC TABULATE output shows four rows for the Canada group (one row for each subsidiary) and the row area cell for Canada spans all four rows for the Canadian subsidiaries. In table description terms, the REGION cell for Canada “spans” the four rows for the SUBSIDIARY values. PROC TABULATE has always produced this type of output.

On the other hand, compare that appearance to the default appearance of PROC REPORT output. PROC REPORT shows the same four rows for the SUBSIDIARY values; however, the value ‘Canada’ appears on the first row of the group and the value for the REGION variable is suppressed for the other three rows of the group. And, the table’s border lines for the four rows are visible in the cell for Canada. This is the default PROC REPORT behavior. It was possible, with PROC REPORT, to put a value for the GROUP or ORDER variable on each row for REGION, but it was not possible to have row spanning with PROC REPORT that would look like TABULATE output.

Sometimes, however, you need to have the capabilities of PROC REPORT (such as CALL DEFINE statements, or COMPUTE blocks) for your report and you still want the cosmetics of the spanning header. That’s the reason for the SPANROWS option for PROC REPORT.

If you add the SPANROWS option to the PROC REPORT statement, you can get PROC REPORT to create spanning cells for GROUP or ORDER usage variables.

Use SPANROWS Option

proc report data=shoes  
         nowd SPANROWS;

PROC REPORT Output with SPANROWS
There are other benefits to the use of SPANROWS, such as in paged destinations output (like RTF and PDF output), where the GROUP or ORDER variable breaks across pages. But I’ll let you experiment with that on your own. For more information about the SPANROWS option with PROC REPORT, refer to these support.sas.com topics, Tech Support Notes and PROC REPORT documentation.

Complete Code

Default Behavior:

proc sort data=sashelp.shoes out=shoes;
  where (product contains 'Casual') and
        (region = 'Canada' or region='Pacific');
  by region product;
run;
 
 
ods listing close;
ods html file='c:\temp\default_behavior.html' style=sasweb;
 
 
proc tabulate data=shoes f=dollar10.;
title 'TABULATE Default Behavior';
  class region subsidiary product;
  var sales;
  table region*subsidiary all=' '*{style=Header},
        product='Product Sales'*sales=' '*(sum mean);
run;
 
proc report data=shoes nowd;
title 'REPORT Default Behavior';
 
 
  column region subsidiary product,(sales sales=savg);
 
  define region / group 
         style(column)=Header;
  define subsidiary / group
         style(column)=Header;
  define product / across 'Product Sales';
  define sales / sum 'Sum' f=dollar10.;
  define savg / mean 'Mean' f=dollar10.;
  rbreak after / summarize style=Header;
run;
 
ods html close;

SPANROWS Option Behavior:

ods html file='c:\temp\spanrows_behavior.html' style=sasweb;
 
proc report data=shoes nowd SPANROWS;
title 'REPORT Output with SPANROWS';
  column region subsidiary product,(sales sales=savg);
  define region / group 
         style(column)=Header;
  define subsidiary / group
         style(column)=Header;
  define product / across 'Product Sales';
  define sales / sum 'Sum' f=dollar10.;
  define savg / mean 'Mean' f=dollar10.;
  rbreak after / summarize style=Header;
run;
 
ods html close;
Tags
Share

About Author

Cynthia Zender

Cynthia Zender is an instructor and course developer at SAS Institute. With Lauren Haworth and Michele Burlew, she has co-authored the book "Output Delivery System: The Basics and Beyond."

15 Comments

  2. Mike on

    Cynthia,
    Can the below calls that work be done in one call (combine style and format in one call)? I'm stumped with this one. I've Googled/researched/tested for hours... Thanks.
    if quantity.sum > 100 then do ;
    call define('total_price.sum', 'format', 'myformat.');
    call define('total_price.sum', 'style','style=[background=cyan ]');
    end ;

    Reply
    • Cynthia on

      Hi: Well, to combine the FORMAT argument and the STYLE argument, the CALL DEFINE statement would have to work differently than it does. The arguments are fixed for CALL DEFINE: 3 arguments -- arg1 = column to impact; arg2 = what attribute you are impacting or changing (FORMAT, STYLE, URL), arg3 = correct syntax for how to make arg2 change.

      It would be hard to specify 2 separate values for argument 2 and then 2 separate values for argument 3 in one CALL DEFINE statement. Sort of a parsing nightmare on the PROC REPORT side of things and sort of a quoting and coding nightmare on the programmer side of things.

      Your code is as compact as it's going to get.

      cynthia

      Reply
  3. Mike on

    Thanks--that's a nice feature too. I ended up moving the code to REPORT because I didn't want the tall, mostly white space box in the first row of data when I dynamically filled my boxes. See the box with 58680.40 in your linked example under "FILLING THE BIG WHITE BOX". The extra tall ones looked out of place once highlighted in color. REPORT took care of that issue and it turned out to be a relatively easy conversion. Thanks again.

    Reply
  4. Mike on

    Cynthia,
    Nice explanation of SPANROWS. I'm looking for the "REPORT Output with SPANROWS" look without rebuilding some inherited PROC TABULATE code. Can tabulate eliminate the tall cell for the first row of data (a border line would be fine). Normally I'd not pay attention to the extra white space above the "Montreal" values BUT I dynamically highlight certain cells based on value and the extra tall ones look a bit odd. Thanks.

    Reply
  5. Rama on

    Hi,
    Very Nice.
    But my requirement is to remove the border line above the columns(Region and Subsidiary) with Proc report like it is with Proc Tabulate.
    My proc report output should look like proc tabulate even on the left top.
    Appreciate your support.

    Reply
  6. Lori on

    Hi Cynthia -
    SPANROWS is not a cosmetic difference to those users of your output with visual impairments. In a culture that reads left-to-right, having blank cells in your first columns tells the reader using magnification that your row is blank or is missing data. It is entirely erroneous for a screen reader because your grouping is implied by contiguous white space that only a sighted user can interpret. SPANROWS finally allows the grouping to be made explicit and accurately rendered by assistive technologies. I was finally able to stop manual correcting my HTML output once the option existed and relayed my gratitude to the SAS programmer responsible.
    Thanks,
    Lori

    Reply
  7. Cynthia Zender on

    Hi:
    That's interesting. I haven't seen any issues with either EG 5.1 or EG 6.1 and SPANROWS and there are no Tech Support notes about such a problem. If you have code that replicates the issue in EG 4.3, I'd recommend opening a track with Tech Support, so that they can report the issue to the developers to make sure that it's fixed.
    . . .
    To open a track with Tech Support, send email to support@sas.com, or fill out the form at this link: http://support.sas.com/ctx/supportform/createForm or you can call them at 919-677-8008 to open a track.
    . . .
    cynthia

    Reply
  8. Fari on

    I noticed that SPANROWS in sas 4.3 EG (SAS 9.02) can cause malfunction creating tables. If so just block SPANROWS in proc report.

    Reply
  9. Scott Moore on

    As fantastic as SPANROWS is, I've run into a bit of trouble with how it operates in combination with ODS PDF. When the spanned row contains a large formatted text value that wraps to multiple lines, it increases the height of the entire first row when what I would like it to do is just expand into the spanned area. I don't observe the same behavior in HTML but I have to produce PDF outputs for my reporting task. Any tips for solving this problem?

    Reply
  10. Kevin Morgan on

    Hi Cynthia:YAY! no more redundant computed text variables.
    The executives in our organization didn't realize it but the spanrows behaviour between proc tabulate and report was the most most commented difference on reports coming from our unit. In fact, when using an excel destination an empty row was down right confusing.
    This is definately an option we will utilize in future reports.
    Thanks and Regards,
    Kevin

    Reply
  11. Mark Jordan on

    This was the tipping point for me, Cynthia, as the "default" behavior in PROC REPORT was something I wasn't fond of and often ran up against. But you've finally made a fan out of me. I've already used this tip & PROC REPORT (along with the ExcelXP ODS tagset) to make short work of a couple of real-world problems. Thanks!

    Reply

Leave A Reply

Back to Top