The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,513
Accepted Solution

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

 

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

 

aaa1.PNG

 

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;

 

 

 

 

 

 


Accepted Solutions
Solution
‎11-09-2016 07:46 PM
New Contributor
Posts: 3

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

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;

View solution in original post


All Replies
New Contributor
Posts: 3

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

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;

 

Trusted Advisor
Posts: 1,513

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

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;

 

Solution
‎11-09-2016 07:46 PM
New Contributor
Posts: 3

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

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;
Grand Advisor
Posts: 9,584

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


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;
Trusted Advisor
Posts: 1,513

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

Thank you all.

These proc report compute blocks sure are tricky to get right.

All good now i think!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 492 views
  • 3 likes
  • 3 in conversation