The SAS Output Delivery System and reporting techniques

PROC Report CELL Coloring condition

Reply
Frequent Contributor
Posts: 78

PROC Report CELL Coloring condition

 

Could somebody give me idea I have following variables :

    COUNT        8.                              
    DATE         8.                              
    MONTH        8.                    
    SUBSYS       8.                    
    TIME         8.                    
    VSP_DAY      8.                    
    VSP_MONTH    8.                    
    VSP_YEAR     8.   ;                
                          

 

Which I am using in below PROC Report and below report output is produced However what I am looking is to color the Cell which match the MAX column so that that clearly give me indication which month is maximum out of all those months.s

 

PROC REPORT DATA=BASEVSP.BREACHES  NOWD                       
      STYLE(REPORT)={CELLSPACING=2                            
                     BACKGROUND=CXEEEEEE                      
                     BORDERWIDTH=3                            
                     BORDERCOLOR=CX003399} /* BOFA BLUE */    
      STYLE(HEADER)={FONT_SIZE=4                              
                     FONT_WEIGHT=BOLD                         

                     FOREGROUND=CX003399}                           
      STYLE(COLUMN)={BORDERCOLOR=BLACK                              
                     FONT_SIZE=2                                    
                     JUST=CENTER                                    
                     BORDERCOLORLIGHT=BLACK                         
                     BORDERCOLORDARK=BLACK};                        
  COLUMN SUBSYS FLAG MONTH COUNT ;                                  
  DEFINE FLAG     / Computed NoprinT ;                              
  DEFINE SUBSYS   / GROUP 'SubSys' WIDTH=10 ;                       
  DEFINE MONTH    / ACROSS 'MonTh' F=mn_name. WIDTH=10 ;            
  DEFINE COUNT    / ANALYSIS MAX 'Max Of Months Breach' WIDTH=10;   
                                                                    
COMPUTE COUNT;                                                      
IF COUNT.MAX = COUNT THEN DO;                                       
CALL DEFINE(_COL_,'STYLE','STYLE={BACKGROUNDCOLOR=RED}');           
                     END;                                           
ENDCOMP;                                                            

 

 

MonTh

 

SubSys

Aug

Feb

Sep

Max Of Months Breach

53809

N

1

N

1

55793

N

1

N

1

65239

1

N

N

1

Super User
Super User
Posts: 7,432

Re: PROC Report CELL Coloring condition

Personally I would have a datastep before the report that works this item out and has a binary flag, then this flag can be in teh report, but not printed, and your compute could use that flag to highlight the cell:

data inter;

  set have; 

  array months{12};

  if count=max(of months{*}) then flag=1;

  else flag=0;

run;

proc report...;

  columns ... flag;

  define flag / noprint;

...

  compute..

    if flag=1 then call define...;

...

SAS Super FREQ
Posts: 8,745

Re: PROC Report CELL Coloring condition

Well, I'm not sure what is happening with the OP's original code. I don't see how PROC REPORT would even produce that output. How do you get an "N" for the value underneath some of the months? You don't show anything nested with MONTH in the COLUMN statement, so all you will get is the count (the SAS statistic) under each cell.

 

Also, your COMPUTE block is for COUNT, the variable. But what you want to highlight, from the way I read your description, is the MONTH that is equal to the MAX of the COUNT column on the report.

 

I would also expect that your code might not produce any output and that you would see this note in the log:

NOTE: Variable COUNT is uninitialized.

 

Because of this IF statement:

 IF COUNT.MAX = COUNT THEN DO;

     CALL DEFINE(_COL_,'STYLE','STYLE={BACKGROUNDCOLOR=LIGHTRED}');

END;

 

I would expect that your IF statement would be something like this (in pseudo-code)

 IF <count of the month> = COUNT.MAX THEN DO;

     CALL DEFINE(<refer to month column>,'STYLE','STYLE={BACKGROUNDCOLOR=LIGHTRED}');

END;

 

But your current COMPUTE block and IF statement aren't doing that. Can you post a sample of your ALL your code, including your ODS statement and post a program that creates some fake data??? Otherwise, everyone has to just guess at the structure of the data. And, can you explain the way you intend to use the FLAG item on the report???

 

cynthia

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 324 views
  • 0 likes
  • 3 in conversation