%let name=bar_table;
filename odsout '.';

/*
This is a SAS/Graph imitation (and enhancement) of the following type of chart:
http://chandoo.org/img/dashboards/dw/health-care-dashboard-large.png
*/

%let days=60;

data rawdata; 
length alert $1 fvalue $20;
input name $ 1-50 value1 value2 value3 value4 percent_of_target alert fvalue;
/* 
fvalue is the formatted value of the final value - format it how
you want it to show up in the table ... not all values in the table
need to be the same format.
*/
datalines;
Recovery capitated contracts                        .03    .03  .04  .05     .05 Y 5%
Event Gloss                                         .07    .06  .06  .05     .05 Y 5%
Debugging payables                                  .05    .06  .05  .06     .41 Y 6%
Short-term borrowings                               1.1    1.05 1.05 1.06    .05 Y 106%
Indebtedness                                        .69    .59  .59   .59    .05 N 59%
Operating Margin                                    0      .1    0     0     .05 Y 0%
ROE                                                 .045  .035  .025  .050   .28 N 5%
Acid test accounts receivable                       .54   .52   .56   .55    .50 N 0.55
Expenditure budget execution                        .54   .55   .54   .56    .60 N 56%
Revenue budget execution                            .59   .60   .59   .61    .59 N 59%
Working Capital                                     1.6   1.7   1.8   1.6    .05 Y -
General liquidity (Strength)                        1.50  1.53  1.72  1.69   .99 N 1.69
Current Ratio                                        .90   .94   .98   .94   .35 Y 0.94
;
run;

/* 
This allows me to plot them in the original data order, which is often important.
If you want to plot them in some other order, sort the rawdata accordingly,
before setting this 'order' variable.
(The data is plotted in this order, bottom-to-top)
*/
data rawdata; set rawdata;
order+1;  
run;

/* 
count how many, to figure out how many ypixels (vertical size) you need 
(about 30 pixels per each sparkline, and ~33 for titles & footnotes)
*/
proc sql;
select 33+30*count(*) into :ypix separated by ' ' from rawdata;
quit; run;

/* Create an obsn for each value (similar to transposing the data) */
data plotdata; set rawdata;
date=1; value=value1; output;
date=2; value=value2; output;
date=3; value=value3; output;
date=4; value=value4; output;
run;


/* Create macro variables containing the min & max date in the data */
proc sql;
select min(date) into :mindate separated by ' ' from plotdata;
select max(date) into :maxdate separated by ' ' from plotdata;
select max(date)-min(date) into :days_by separated by ' ' from plotdata;
select count(unique(name)) into :y_max separated by ' ' from plotdata;
quit; run;


/* calculate the 'normalized' (0-1) representation of the value */
proc sql;
create table plotdata as
select plotdata.*, min(value) as min_value, max(value) as max_value
from plotdata
group by name;
quit; run;
data plotdata; set plotdata;
/* the .80 puts a little bit of gap at the top of each line, for visual spacing */
norm_value=.80*(value-min_value)/(max_value-min_value)+.10;
run;

proc sql;
create table plotdata as
select unique *, min(norm_value) as min_norm, max(norm_value) as max_norm
from plotdata 
group by name;
quit; run;


/* apply an offset to each line */
proc sort data=plotdata out=plotdata;
by name date;
run;
data plotdata; set plotdata;
by name;
norm_value=norm_value+(order-1);
output;
/* this works in conjunction with 'skipmiss' */
if last.name then do;
 value=.;
 norm_value=.;
 output;
 end;
run;


/* Annotate the text in the blank/offset space, to make the plot look like a table */
proc sql;
create table anno_table as select * from plotdata having min_norm^=.;
/* Now, just get 1 unique value for each name */
create table anno_table as
select unique order, name, min_value, max_value, percent_of_target, alert, fvalue
from anno_table;
quit; run;


data anno_table; set anno_table;
length text $65 color $8 style $35;
function='label'; when='a';
ysys='2'; y=order-1;
xsys='1'; 
x=1; position='3'; text=trim(left(name)); output;
x=50; position='2'; text=trim(left(fvalue)); output;
if alert='Y' then do;
 x=45; position='2'; style='albany amt/unicode'; color='red'; text='25cf'x; output;
 end;
/* gray area behind annotated bar */
when='b'; 
function='move'; x=55; y=order-1; output;
function='bar'; x=80; y=order; style='solid'; color='grayf3'; output;
/* simulate a gradient shaded bar chart */
when='a';
do loop_percent = 0 to percent_of_target by .005;
 if loop_percent >= .90 then color='cx006837'; 
 else if loop_percent >= .80 then color='cx1A9850'; 
 else if loop_percent >= .70 then color='cx66BD63'; 
 else if loop_percent >= .60 then color='cxA6D96A'; 
 else if loop_percent >= .50 then color='cxD9EF8B'; 
 else if loop_percent >= .40 then color='cxFEE08B'; 
 else if loop_percent >= .30 then color='cxFDAE61'; 
 else if loop_percent >= .20 then color='cxF46D43'; 
 else if loop_percent >= .10 then color='cxD73027'; 
 else if loop_percent >= .00 then color='cxA50026'; 
 else color='pink';
 /* don't draw past end of 100% length bar, even if % > 100% */
 if loop_percent <=1.00 then do;
  x=55+(80-55)*loop_percent;
  function='move'; y=order-1+.10; output;
  function='draw'; y=order-.10; output;
  end;
 end;
color='';
run;


data anno_lines;
length function color $8 text $100;
xsys='1'; ysys='1'; when='a';
color="graybb";
function='move';  x=0; y=0; output;
 function='draw'; y=100; output;
function='move';  x=100; y=0; output;
 function='draw'; y=100; output;
function='move';  x=55; y=0; output;
 function='draw'; y=100; output;
function='move';  x=80; y=0; output;
 function='draw'; y=100; output;

/* column headings */
function='label'; color=''; position='2'; y=100;
x=20; text="Key figures"; output;
x=50; text="Actual"; output;
x=67; text="% of target"; output;
x=90; text="Trend"; output;
run;



goptions device=png xpixels=600 ypixels=&ypix;

ods listing close;
ODS HTML path=odsout body="&name..htm"
 (title="Medical Metrics Table") 
 style=htmlblue;

goptions ftitle="albany amt" ftext="albany amt" htitle=14pt htext=8pt;
goptions ctext=gray33;

symbol1 value=none interpol=join color=gray22; 

axis1 style=0 label=none major=none minor=none order=(0 to &y_max) 
 value=none offset=(0,0);

/* 
The offset controls the amount of space to the left & right of the gplot line.
(this is the space that you annotate the table into.)
You'll want a *lot* of space to the left (95), and a little to the right (3).
*/
axis2 style=0 label=none major=none minor=none order=(&mindate to &maxdate by &days_by) 
 value=none offset=(95,3);

title1 ls=1.5 "Medical Metrics Table Graph";
title2 height=20pt " ";

footnote1 j=r c=gray "Note: Each sparkline is independently auto-scaled.";

proc gplot data=plotdata anno=anno_table;
plot norm_value*date=1 / skipmiss noframe
 autovref cvref=graybb  /* I annotate the reflines going in the other direction */
 vaxis=axis1 haxis=axis2
 anno=anno_lines
 des='' name="&name";
run;

quit;
ods html close;
ods listing;