As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Repeating Group variable values in PROC REPORT

From sasCommunity
Jump to: navigation, search

The Problem

I was working on a summary report with several group variables. As you know, when you display group (or order) variables in PROC REPORT, only the first row containing a particular value with display that value; following rows will show a blank. This sample program and PROC REPORT show the problem (the sample data are somewhat complex; ignore that and take my word for it that the test data set will contain repetitions of the group variables HasMemberships, ProdLineMatch, ProdLine, and Mbsh_MCProd):

SAS Code to Demonstrate the Problem

data test;
   drop i;
   do HasMemberships = 'y', 'n', 'y';
      do ProdLineMatch = 'y', 'n', 'y';
         if ranuni(92345) > .4 then 
            do ProdLine = 'M', 'C', 'R', ' ';
               do Mbsh_MCProd = 'C', 'R', 'M', ' ';
                  if ranuni(87890) > .6 then 
                     do i = 1 to round(ranuni(95605)*5-1);
                        _freq_ = round(ranuni(94612)*100);
                        if hasmemberships = 'n' then 
                           do;
                           mbsh_mcprod = ' ';
                           prodlinematch = 'n';
                           end;
                        output;
                     end;
               end;
            end;
      end;
   end;
run;
 
title 'Default Display of Repeated Group Values';
 
proc report data=test missing nofs;
   column HasMemberships ProdLineMatch ProdLine Mbsh_MCProd
          _freq_;
     
   define HasMemberships / group width=14;
   define ProdLineMatch  / group width=13;
   define ProdLine       / group width=8;
   define Mbsh_MCProd    / group width=11;
      
   define _freq_ / sum format=comma12.0 width=12 'Freq';
       
run;

Output that Demonstrates the Problem

HasMemberships 	ProdLineMatch 	ProdLine 	Mbsh_MCProd 	Freq
n 	n 	  	  	281
  	  	C 	  	479
  	  	M 	  	252
  	  	R 	  	689
y 	n 	  	M 	175
  	  	  	R 	151
  	  	C 	R 	123
  	  	M 	  	143
  	  	  	M 	7
  	  	R 	  	243
  	y 	C 	C 	61
  	  	  	M 	191
  	  	M 	  	175
  	  	R 	C 	235

The Solution: Compute blocks

As you can see, it can be difficult to tell which real values are behind each cell. Having the group values repeat in each cell would make the table much easier to read. Unfortunately, in SAS 9.1 there's no way to make that happen automatically.

You can, however, do it by using compute blocks. I'm not going to explain the code, but here's an example:

proc report data=test missing nofs;
   column HasMemberships ProdLineMatch ProdLine Mbsh_MCProd
          _freq_;
  
   define HasMemberships / group width=14;
  
   define ProdLineMatch  / group width=13;
   compute before ProdLinematch;
      hold_ProdLinematch = ProdLinematch;
   endcomp;
   compute ProdlineMatch;
      if _break_ = ' ' then
         ProdLinematch = hold_ProdLinematch;
   endcomp;
  
   define ProdLine       / group width=8;
   compute before ProdLine;
      hold_ProdLine = ProdLine;
   endcomp;
   compute Prodline;
      if _break_ = ' ' then
         do;
         ProdLine = hold_ProdLine;
         end;
   endcomp;
  
   define Mbsh_MCProd    / group width=11;
  
   define _freq_ / sum format=comma12.0 width=12 'Freq';
   
run;

Output Using Compute blocks

HasMemberships 	ProdLineMatch 	ProdLine 	Mbsh_MCProd 	Freq
n 	n 	  	  	281
  	n 	C 	  	479
  	n 	M 	  	252
  	n 	R 	  	689
y 	n 	  	M 	175
  	n 	  	R 	151
  	n 	C 	R 	123
  	n 	M 	  	143
  	n 	M 	M 	7
  	n 	R 	  	243
  	y 	C 	C 	61
  	y 	C 	M 	191
  	y 	M 	  	175
  	y 	R 	C 	235