topic PROC Report CELL Coloring condition in ODS and Base Reporting

PROC Report CELL Coloring condition

rkumar23 — Thu, 29 Oct 2015 11:18:41 GMT

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

Re: PROC Report CELL Coloring condition

RW9 — Thu, 29 Oct 2015 11:45:56 GMT

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...;

...

Re: PROC Report CELL Coloring condition

Cynthia_sas — Thu, 29 Oct 2015 22:54:38 GMT

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