BookmarkSubscribeRSS Feed
BruceBrad
Lapis Lazuli | Level 10

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

2 REPLIES 2
Ksharp
Super User

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;

x.png

Ksharp
Super User
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;

x.png

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 932 views
  • 2 likes
  • 2 in conversation