Traffic lighting based on ranks

[BruceBrad]

I have a report with three columns of numbers (var1, var2 var3). I would like to highlight each column (independently) based on the within-column rank. Eg In column 1 the third of values with the highest values for var1 would be red, the third with lowest values green, and similarly for the other two columns.

I think I could do this by running proc univariate to get the p33 and p67 values for each variable, using these to create a format for each variable and then using this in the output (Proc report, tabulate or print). This sounds very messy. Is there an easier way? For example, can proc report compute blocks do calculations within columns (such as ranks?).

[Ksharp]

That is really not easy . I took some code from here:

http://blogs.sas.com/content/graphicallyspeaking/2015/11/30/7180/

Good Luck.

proc rank data=sashelp.class out=class ties=dense;
var age weight height;
ranks r_age r_weight r_height;
run;

%let dsid=%sysfunc(open(class));
%let n=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

%let minbin=1;
%let maxbin=&n;
%let BinInt=1;
data AttrMap;
  length FillColor $8 LineColor $8;
  id='Hist';
  ghigh=192; /*--High value for Green--*/
  rhigh=255; /*--High value for Red--*/

  mid=(&minbin + &maxbin) / 2;
  LineColor='CX000000';
 
  do val=&minbin to &maxbin by &BinInt;
    value=put(val, 5.0);
    if val < mid then do;
      g=ghigh; b=0; r=rhigh*(val-&minbin)/ (mid-&minbin);
    end;
    else do;
      r=rhigh; b=0; g=ghigh*(1-((val-&minbin) - (mid-&minbin))/ (mid-&minbin));
    end;
    fillcolor='CX' || put(r, hex2.) || put(g, hex2.) || put(b, hex2.); 
    output;
  end;
run;
data fmt;
if _n_ eq 1 then do;
 if 0 then set attrmap;
 declare hash h(dataset:'attrmap');
 h.definekey('val');
 h.definedata('fillcolor');
 h.definedone();
end;
 set class(keep=age r_age rename=(age=start r_age=k) in=ina)
     class(keep=weight r_weight  rename=(weight =start r_weight=k) in=inb)
     class(keep=height r_height rename=(height =start r_height=k) in=inc);
 h.find(key:k);
 retain type 'N';
 length fmtname $ 10;
 if ina then fmtname='age'; 
  else if inb then fmtname='weight';
   else if inc then fmtname='height';
 rename fillcolor=label;
 keep fillcolor fmtname start;
run;
proc sort data=fmt out=want_fmt nodupkey;by fmtname start;run;
proc format cntlin=want_fmt ;
run;

proc report data=class nowd ;
column age weight height;
define age/display style={background=age.};
define weight/display style={background=weight.};
define height/display style={background=height.};
run;

[Ksharp]

data x;
 set sashelp.class(drop=age);
 age+1;
 run;
proc rank data=x out=class ties=dense;
var age weight height;
ranks r_age r_weight r_height;
run;

%let dsid=%sysfunc(open(class));
%let n=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

%let minbin=1;
%let maxbin=&n;
%let BinInt=1;
data AttrMap;
  length FillColor $8 LineColor $8;
  id='Hist';
  ghigh=192; /*--High value for Green--*/
  rhigh=255; /*--High value for Red--*/

  mid=(&minbin + &maxbin) / 2;
  LineColor='CX000000';
 
  do val=&minbin to &maxbin by &BinInt;
    value=put(val, 5.0);
    if val < mid then do;
      g=ghigh; b=0; r=rhigh*(val-&minbin)/ (mid-&minbin);
    end;
    else do;
      r=rhigh; b=0; g=ghigh*(1-((val-&minbin) - (mid-&minbin))/ (mid-&minbin));
    end;
    fillcolor='CX' || put(r, hex2.) || put(g, hex2.) || put(b, hex2.); 
    output;
  end;
run;
data fmt;
if _n_ eq 1 then do;
 if 0 then set attrmap;
 declare hash h(dataset:'attrmap');
 h.definekey('val');
 h.definedata('fillcolor');
 h.definedone();
end;
 set class(keep=age r_age rename=(age=start r_age=k) in=ina)
     class(keep=weight r_weight  rename=(weight =start r_weight=k) in=inb)
     class(keep=height r_height rename=(height =start r_height=k) in=inc);
 h.find(key:k);
 retain type 'N';
 length fmtname $ 10;
 if ina then fmtname='age'; 
  else if inb then fmtname='weight';
   else if inc then fmtname='height';
 rename fillcolor=label;
 keep fillcolor fmtname start;
run;
proc sort data=fmt out=want_fmt nodupkey;by fmtname start;run;
proc format cntlin=want_fmt ;
run;

proc report data=class nowd ;
column age weight height;
define age/display style={background=age.};
define weight/display style={background=weight.};
define height/display style={background=height.};
run;