Resolving conflicts over ODS row and cell coloring

[spjcdc]

I've got a report that i want to create where in some cases I want just certain cells to be colored yellow, but in other cases I want an entire row to be colored green.  If a row is green I don't want any yellow on it, even if the condition that turns yellow on is true.  Is there a way to affect which attribute takes precedence when two different call define statements are referring to the same cell?

proc report data=sashelp.class nowd ;

column name age sex weight ;

compute age ;

     if age in (13,15) then call define("age","style","Style={background=yellow}") ;

endcompute ;

compute sex ;

     if sex='M' then call define(_row_,"style","style={background=green}") ;

endcompute ;

run ;

The preceeding code colors the rows for males as green, but if their age is either 13 or 15 then the age cell is highlighted yellow.  What i want is for the row color to take precedence so that the entire row is green for males even if they're 13 or 15.  I suppose I could construct an IF statement that would check for males before checking the age, but in the real problem I'm trying to solve that will mean modifying dozens of compute blocks, and I'd rather not do that if there's another way.

Any ideas?

[BrunoMueller · Accepted solution]

Since all numeric variables are by default ANALYSIS variables, you can not use "age" to access the value but rather "age.sum".

You can also define age to be a DISPLAY variable and then your code will work. See example below:

proc report data=sashelp.class nowd;
  column name age sex weight;
  define age / display;

  compute age;

    if age in (13,15) then
      call define("age","style","Style={background=yellow}");
  endcomp;

  compute sex;

    if sex='M' then
      call define(_row_,"style","style={background=green}");
  endcomp;
run;

You can also add a dummy column and do all the processing in just one compute block, this will make it easier if the logic gets more complicated, see also an example below:

proc report data=sashelp.class nowd;
  column name age sex weight _dummy;
  define _dummy / noprint;

  compute _dummy;

    if sex='M' then do;
      call define(_row_,"style","style={background=green}");
    end;

    if age.sum in (13, 15) then do;
      call define("age.sum","STYLE","Style={background=yellow}");
    end;
  endcomp;
run;

[spjcdc]

Bruno,

Thanks for your response.  But what you suggested didn't work.  In both cases the cell containing age was yellow regardless of whether Sex='M' or 'F'. What I want is the cell to be all green if Sex='M'. 

However your use of a dummy variable works if I did something like the following:

if sex='M' then do ;

...

end ;

else if age.sum in (13,15) then do ;

...

end ;

end ;

The actual problem I'm trying to solve is a case where I have about 100 variable pairs (e.g. Var1_actual and Var1_expected) and I want to highlight any pair with yellow where the two values don't match.  However if for one particular variable pair (ID_actual and ID_expected) if they don't match I want the entire row a different color.  I already have the code that colors the cells yellow if the values differ.  What I want to do is to add the feature that changes the whole row to a different color if the two IDs don't match.  I could use a dummy variable as you suggested but that would require a complete re-write of the code.  I'm trying to figure out if there's some other way to do what I want done without a substantial re-write. 

Thanks,

Steve

[BrunoMueller]

Try to use the dummy var only to color the complete line.

Another approach would be the use of arrays in the compute block. Using this technique it is quite simple to compare a 100 value pairs, see example below. I guess part of the code will be created with a macro if you have a 100 value pairs to compare.

data have;
  id_actual = 1;
  id_expected = 1;
  Var1_actual = 10;
  Var1_expected = 10;
  Var2_actual = 10;
  Var2_expected = 12;
  Var3_actual = 11;
  Var3_expected = 11;
  output;
  id_actual = 2;
  id_expected = 3;
  Var1_actual = 10;
  Var1_expected = 12;
  Var2_actual = 10;
  Var2_expected = 10;
  Var3_actual = 13;
  Var3_expected = 14;
  output;
run;

proc report data=have nowindows;
  column
    id_actual id_expected
    var1_actual var1_expected
    var2_actual var2_expected
    var3_actual var3_expected
    _dummy
  ;
  define id_actual / display;
  define id_expected / display;
  define var1_actual / display;
  define var1_expected / display;
  define var2_actual / display;
  define var2_expected /  display;
  define var3_actual / display;
  define var3_expected / display;
  define _dummy / noprint;

  compute _dummy;
    array xAct{*} var1_actual var2_actual var2_actual;
    array xExp{*} var1_expected var2_expected var2_expected;

    if id_actual ne id_expected then do;
      call define(_row_, "style", "style={background=green}");
    end;
    else do;
      do i = 1 to dim(xAct);
        if xAct{i} ne xExp{i} then do;
          call define(vname(xAct{i}), "STYLE", "Style={background=yellow}");
          call define(vname(xExp{i}), "STYLE", "Style={background=yellow}");
        end;
      end;
    end;
  endcomp;
run;

[spjcdc]

Thanks.  I wasn't aware that you could use array statements in Proc Report.  That'll simplify things significantly.

Steve