topic Re: How to color the row in proc report depending on subcolumn values in ODS and Base Reporting

How to color the row in proc report depending on subcolumn values

ChrisNZ — Wed, 09 Nov 2016 01:31:14 GMT

I have coloured the second line (because it is not the first line for row R1).

I also want to colour the third line (because a count is equal to 3).

Any idea how? My attempts (commented out below) fail.



data HAVE;
  input ROW $ COL $ VALUE $ ;
cards;
R1 C1 x
R1 C2 y
R1 C1 x1
R1 C2 y
R2 C1 x
R2 C2 z
R2 C1 x
R2 C2 z
R2 C2 z
run;

proc freq data=HAVE noprint;
  table ROW*COL*VALUE/out=FREQ  ;
run;

data LINED;  
  set FREQ;
  by ROW COL;
  if first.COL then LINE=0;
  LINE+1;
run;

proc sort; 
  by ROW LINE COL;
run;

proc report data=LINED nowd ;
  column ROW LINE COL,(VALUE COUNT);
  define ROW   /group;
  define LINE  /group noprint;
  define COL   /across ' ';
  define VALUE /display  ;
  define COUNT /analysis '#' ;
  compute LINE;
     if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
    * if COUNT=3  then call define(_row_,"style","style={background=cxEEFFFF}");
    * if _C5_=3 | _C3_=3 then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
run;

Re: How to color the row in proc report depending on subcolumn values

Dan_B — Wed, 09 Nov 2016 02:45:49 GMT

Hi Chris

I'd generally recommend using OUT to create an output dataset during development. That way you can see the values created within the Proc Report step - sometimes its difficult to understand what is actually happenning in the procedure and having access to this information is useful during development.

This code should give you what your looking for.

Cheers

proc report data=LINED nowd out=work.test;
  column ROW LINE COL,(VALUE COUNT);
  define ROW   /group;
  define LINE  /group noprint;
  define COL   /across ' ';
  define VALUE /display  ;
  define COUNT /analysis '#' ;
  compute LINE;
     if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
    * if COUNT=3  then call define(_row_,"style","style={background=cxEEFFFF}");
    * if _C5_=3 | _C3_=3 then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;

  compute COL;
    if _C4_ = 3 or _C6_=3  then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
run;

Re: How to color the row in proc report depending on subcolumn values

ChrisNZ — Wed, 09 Nov 2016 03:37:25 GMT

Thanks for the out= tip!

2 things:

- why do we need to have column COL as the computed value?

- something seems wrong with inequalities:


proc report data=LINED nowd out=t;
  column ROW LINE COL,(VALUE COUNT);
  define ROW   /group;
  define LINE  /group noprint;
  define COL   /across ' ';
  define VALUE /display  ;
  define COUNT /analysis '#' ;
  compute LINE;
     if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
  compute COL;
     * 3rd line is blue as intended;
     * if _C4_ = 2 then call define(_row_,"style","style={background=cxEEFFFF}");
     * 1st line is blue as intended;
     * if _C4_ = 1 then call define(_row_,"style","style={background=cxEEFFFF}");
     * all lines are blue! not intended ! ;
     * if _C4_ ^in (1,2) then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
run;

Re: How to color the row in proc report depending on subcolumn values

Dan_B — Wed, 09 Nov 2016 04:58:26 GMT

The timing and execution of the compute blocks can be quite different based on the association of report items. In this scenario you may want the compute block to execute when the proc report processes the COUNT column.

There are some good papers that go into quite a bit of detail to explain the sequence of compute blocks. From memory they work from left to right in order of columns (not by the order of your compute block codes).

Once I adjusted the compute block to execute when processing the COUNT column the issue with the IN operator is corrected. You could combine the LINE compute block with the COUNT compute block since LINE column would already be processed by this stage.

proc report data=LINED nowd out=t;
  column ROW LINE COL,(VALUE COUNT);
  define ROW   /group;
  define LINE  /group noprint;
  define COL   /across ' ';
  define VALUE /display  ;
  define COUNT /analysis '#' ;
/*  compute LINE;*/
/*     if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");*/
/*  endcomp;*/
  compute COUNT;
     if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
     * 3rd line is blue as intended;
     * if _C4_ = 2 then call define(_row_,"style","style={background=cxEEFFFF}");
     * 1st line is blue as intended;
     * if _C4_ = 1 then call define(_row_,"style","style={background=cxEEFFFF}");
     * all lines are blue! not intended ! ; 
     * Corrected by adjusting the COMPUTE block to execute when processing of COUNT;
      if _C4_ ^in (1,2) then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
run;

Re: How to color the row in proc report depending on subcolumn values

Ksharp — Wed, 09 Nov 2016 05:48:34 GMT


Put that in the right column.




data HAVE;
  input ROW $ COL $ VALUE $ ;
cards;
R1 C1 x
R1 C2 y
R1 C1 x1
R1 C2 y
R2 C1 x
R2 C2 z
R2 C1 x
R2 C2 z
R2 C2 z
run;

proc freq data=HAVE noprint;
  table ROW*COL*VALUE/out=FREQ  ;
run;

data LINED;  
  set FREQ;
  by ROW COL;
  if first.COL then LINE=0;
  LINE+1;
run;

proc sort; 
  by ROW LINE COL;
run;

proc report data=LINED nowd ;
  column ROW LINE COL,(VALUE COUNT);
  define ROW   /group;
  define LINE  /group noprint;
  define COL   /across ' ';
  define VALUE /display  ;
  define COUNT /analysis '#' ;
  compute LINE;
     if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
  compute count;
     if _C6_=3 | _C3_=3 then call define(_row_,"style","style={background=cxEEFFFF}");
  endcomp;
run;