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

%let barcolor=cxEE9A49;
%let refcolor=graybb;
%let backcolor=grayee;

%let titl2txt=Year: 2004;

/* 
This is a special job to create the drilldown plots for the scen3 and scen3b dashboard
*/

/* First, read in all the data, and then do all the plots */

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met1 REPLACE;
 RANGE='Scenario3$A19:N19';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met1 (drop=a b c d e f g h i j k l m n); set act_met1;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met1; set act_met1 (drop=f2); run;
proc transpose data=act_met1 out=act_met1; by f1; run;
data act_met1 (keep=month Quarter revenue); set act_met1;
metric=f1; revenue=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met2 REPLACE;
 RANGE='Scenario3$A20:N20';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met2 (drop=a b c d e f g h i j k l m n); set act_met2;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met2; set act_met2 (drop=f2); run;
proc transpose data=act_met2 out=act_met2; by f1; run;
data act_met2 (keep=month Quarter profit); set act_met2;
metric=f1; profit=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met3 REPLACE;
 RANGE='Scenario3$A21:N21';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met3 (drop=a b c d e f g h i j k l m n); set act_met3;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met3; set act_met3 (drop=f2); run;
proc transpose data=act_met3 out=act_met3; by f1; run;
data act_met3 (keep=month Quarter order_size); set act_met3;
metric=f1; order_size=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met4 REPLACE;
 RANGE='Scenario3$A22:N22';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met4 (drop=a b c d e f g h i j k l m n); set act_met4;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met4; set act_met4 (drop=f2); run;
proc transpose data=act_met4 out=act_met4; by f1; run;
data act_met4 (keep=month Quarter market_share); set act_met4;
metric=f1; market_share=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met5 REPLACE;
 RANGE='Scenario3$A23:N23';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met5 (drop=a b c d e f g h i j k l m n); set act_met5;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met5; set act_met5 (drop=f2); run;
proc transpose data=act_met5 out=act_met5; by f1; run;
data act_met5 (keep=month Quarter satisfaction); set act_met5;
metric=f1; satisfaction=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met6 REPLACE;
 RANGE='Scenario3$A24:N24';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met6 (drop=a b c d e f g h i j k l m n); set act_met6;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met6; set act_met6 (drop=f2); run;
proc transpose data=act_met6 out=act_met6; by f1; run;
data act_met6 (keep=month Quarter on_time); set act_met6;
metric=f1; on_time=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_met7 REPLACE;
 RANGE='Scenario3$A25:N25';
 GETNAMES=NO; MIXED=NO; RUN;
data act_met7 (drop=a b c d e f g h i j k l m n); set act_met7;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_met7; set act_met7 (drop=f2); run;
proc transpose data=act_met7 out=act_met7; by f1; run;
data act_met7 (keep=month Quarter new_cust); set act_met7;
metric=f1; new_cust=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                              


data act_met;
 merge act_met1 act_met2 act_met3 act_met4 act_met5 act_met6 act_met7;
run;
data act_met; set act_met;
 if month in (1 2 3) then Quarter='Q1';
 if month in (4 5 6) then Quarter='Q2';
 if month in (7 8 9) then Quarter='Q3';
 if month in (10 11 12) then Quarter='Q4';
run;
proc sql;
create table act_met_sum as
select unique
 month,
 Quarter,
 revenue      as m_revenue,
 profit       as m_profit,
 order_size   as m_order_size,
 market_share as m_market_share,
 satisfaction as m_satisfaction,
 on_time      as m_on_time,
 new_cust     as m_new_cust,
 sum(revenue)      as revenue,
 sum(profit)       as profit,
 avg(order_size)   as order_size,
 avg(market_share) as market_share,
 avg(satisfaction) as satisfaction,
 avg(on_time)      as on_time,
 sum(new_cust)     as new_cust
from act_met
group by Quarter;
quit; run;



PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met1 REPLACE;
 RANGE='Scenario3$A68:E68';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met1 (drop=a b c d e); set tgt_met1;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met1 out=tgt_met1; by f1; run;
data tgt_met1 (keep=Quarter revenue); set tgt_met1;
metric=f1; revenue=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met2 REPLACE;
 RANGE='Scenario3$A69:E69';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met2 (drop=a b c d e); set tgt_met2;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met2 out=tgt_met2; by f1; run;
data tgt_met2 (keep=Quarter profit); set tgt_met2;
metric=f1; profit=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met3 REPLACE;
 RANGE='Scenario3$A70:E70';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met3 (drop=a b c d e); set tgt_met3;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met3 out=tgt_met3; by f1; run;
data tgt_met3 (keep=Quarter order_size); set tgt_met3;
metric=f1; order_size=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met4 REPLACE;
 RANGE='Scenario3$A71:E71';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met4 (drop=a b c d e); set tgt_met4;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met4 out=tgt_met4; by f1; run;
data tgt_met4 (keep=Quarter market_share); set tgt_met4;
metric=f1; market_share=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met5 REPLACE;
 RANGE='Scenario3$A72:E72';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met5 (drop=a b c d e); set tgt_met5;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met5 out=tgt_met5; by f1; run;
data tgt_met5 (keep=Quarter satisfaction); set tgt_met5;
metric=f1; satisfaction=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met6 REPLACE;
 RANGE='Scenario3$A73:E73';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met6 (drop=a b c d e); set tgt_met6;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met6 out=tgt_met6; by f1; run;
data tgt_met6 (keep=Quarter on_time); set tgt_met6;
metric=f1; on_time=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_met7 REPLACE;
 RANGE='Scenario3$A74:E74';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_met7 (drop=a b c d e); set tgt_met7;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_met7 out=tgt_met7; by f1; run;
data tgt_met7 (keep=Quarter new_cust); set tgt_met7;
metric=f1; new_cust=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;  

data tgt_met;
 merge tgt_met1 tgt_met2 tgt_met3 tgt_met4 tgt_met5 tgt_met6 tgt_met7;
run;
data tgt_met; set tgt_met;
 if month in (1 2 3) then Quarter='Q1';
 if month in (4 5 6) then Quarter='Q2';
 if month in (7 8 9) then Quarter='Q3';
 if month in (10 11 12) then Quarter='Q4';
run;

proc sql;
create table tgt_met_sum as
select unique
 Quarter,
 sum(revenue)      as revenue,
 sum(profit)       as profit,
 avg(order_size)   as order_size,
 avg(market_share) as market_share,
 avg(satisfaction) as satisfaction,
 avg(on_time)      as on_time,
 sum(new_cust)     as new_cust
from tgt_met
group by Quarter;
quit; run;


proc datasets;
 modify act_met_sum;
 rename revenue      = a_revenue;
 rename profit       = a_profit;
 rename order_size   = a_order_size;
 rename market_share = a_market_share;
 rename satisfaction = a_satisfaction;
 rename on_time      = a_on_time;
 rename new_cust     = a_new_cust;
run;

proc datasets;
 modify tgt_met_sum;
 rename revenue      = t_revenue;
 rename profit       = t_profit;
 rename order_size   = t_order_size;
 rename market_share = t_market_share;
 rename satisfaction = t_satisfaction;
 rename on_time      = t_on_time;
 rename new_cust     = t_new_cust;
run;

proc sql;
create table metrics as
select act_met_sum.*, 
       tgt_met_sum.t_revenue, tgt_met_sum.t_profit, tgt_met_sum.t_order_size, tgt_met_sum.t_market_share, tgt_met_sum.t_satisfaction, tgt_met_sum.t_on_time, tgt_met_sum.t_new_cust
from act_met_sum left join tgt_met_sum
on act_met_sum.Quarter = tgt_met_sum.Quarter;
quit; run;

data metrics; set metrics;
format a_revenue dollar12.0;
format a_profit dollar12.0;
format a_market_share percent6.0;
format a_on_time percent6.0;

length ev_revenue $12;
length ev_profit $12;
length ev_order_size $12;
length ev_market_share $12;
length ev_satisfaction $12;
length ev_on_time $12;
length ev_new_cust $12;

 pct_revenue=a_revenue/t_revenue;
 if pct_revenue < .6 then ev_revenue='Poor';
 else if pct_revenue < .9 then ev_revenue='Satisfactory';
 else if pct_revenue >=.9 then ev_revenue='Good';

 pct_profit=a_profit/t_profit;
 if pct_profit < .6 then ev_profit='Poor';
 else if pct_profit < .8 then ev_profit='Satisfactory';
 else if pct_profit >=.8 then ev_profit='Good';

 pct_order_size=a_order_size/t_order_size;
 if pct_order_size < .5 then ev_order_size='Poor';
 else if pct_order_size < .75 then ev_order_size='Satisfactory';
 else if pct_order_size >=.75 then ev_order_size='Good';

 pct_market_share=a_market_share/t_market_share;
 if pct_market_share < .65 then ev_market_share='Poor';
 else if pct_market_share < .9 then ev_market_share='Satisfactory';
 else if pct_market_share >=.9 then ev_market_share='Good';

 pct_satisfaction=a_satisfaction/t_satisfaction;
 if pct_satisfaction < .6 then ev_satisfaction='Poor';
 else if pct_satisfaction < .9 then ev_satisfaction='Satisfactory';
 else if pct_satisfaction >=.9 then ev_satisfaction='Good';

 pct_on_time=a_on_time/t_on_time;
 if pct_on_time < .6 then ev_on_time='Poor';
 else if pct_on_time < .9 then ev_on_time='Satisfactory';
 else if pct_on_time >=.9 then ev_on_time='Good';

 pct_new_cust=a_new_cust/t_new_cust;
 if pct_new_cust < .5 then ev_new_cust='Poor';
 else if pct_new_cust < .85 then ev_new_cust='Satisfactory';
 else if pct_new_cust >=.85 then ev_new_cust='Good';

run;

/* This is a missing-value placeholder, to guarantee that
   all the bar charts use the 2 colors in the same way.  */
data foometrics;

length ev_revenue $12;
length ev_profit $12;
length ev_order_size $12;
length ev_market_share $12;
length ev_satisfaction $12;
length ev_on_time $12;
length ev_new_cust $12;

a_revenue=.;
a_profit=.;
a_order_size=.;
a_market_share=.;
a_satisfaction=.;
a_on_time=.;
a_new_cust=.;
Quarter='Q1';

ev_revenue='Poor'; ev_profit='Poor'; ev_order_size='Poor'; ev_market_share='Poor';
 ev_satisfaction='Poor'; ev_on_time='Poor'; ev_new_cust='Poor'; output;

ev_revenue='Satisfactory'; ev_profit='Satisfactory'; ev_order_size='Satisfactory'; ev_market_share='Satisfactory';
 ev_satisfaction='Satisfactory'; ev_on_time='Satisfactory'; ev_new_cust='Satisfactory'; output;

ev_revenue='Good'; ev_profit='Good'; ev_order_size='Good'; ev_market_share='Good';
 ev_Good='Good'; ev_on_time='Good'; ev_new_cust='Good'; output;

run;
data metrics; set metrics foometrics; run;


/* ------------------------------------------------------------------------------------ */


PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvp1 REPLACE;
 RANGE='Scenario3$A29:N29';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvp1 (drop=a b c d e f g h i j k l m n); set act_rvp1;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvp1; set act_rvp1 (drop=f2); run;
proc transpose data=act_rvp1 out=act_rvp1; by f1; run;
data act_rvp1 (keep=month Quarter cabernet); set act_rvp1;
product=f1; cabernet=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                                

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvp2 REPLACE;
 RANGE='Scenario3$A30:N30';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvp2 (drop=a b c d e f g h i j k l m n); set act_rvp2;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvp2; set act_rvp2 (drop=f2); run;
proc transpose data=act_rvp2 out=act_rvp2; by f1; run;
data act_rvp2 (keep=month Quarter zinfandel); set act_rvp2;
product=f1; zinfandel=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                                

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvp3 REPLACE;
 RANGE='Scenario3$A31:N31';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvp3 (drop=a b c d e f g h i j k l m n); set act_rvp3;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvp3; set act_rvp3 (drop=f2); run;
proc transpose data=act_rvp3 out=act_rvp3; by f1; run;
data act_rvp3 (keep=month Quarter merlot); set act_rvp3;
product=f1; merlot=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                                

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvp4 REPLACE;
 RANGE='Scenario3$A32:N32';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvp4 (drop=a b c d e f g h i j k l m n); set act_rvp4;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvp4; set act_rvp4 (drop=f2); run;
proc transpose data=act_rvp4 out=act_rvp4; by f1; run;
data act_rvp4 (keep=month Quarter chardonnay); set act_rvp4;
product=f1; chardonnay=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                                

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvp5 REPLACE;
 RANGE='Scenario3$A33:N33';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvp5 (drop=a b c d e f g h i j k l m n); set act_rvp5;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvp5; set act_rvp5 (drop=f2); run;
proc transpose data=act_rvp5 out=act_rvp5; by f1; run;
data act_rvp5 (keep=month Quarter sauvignan_blanc); set act_rvp5;
product=f1; sauvignan_blanc=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                                

data act_rvp;
 merge act_rvp1 act_rvp2 act_rvp3 act_rvp4 act_rvp5;
run;
data act_rvp; set act_rvp;
 if month in (1 2 3) then Quarter='Q1';
 if month in (4 5 6) then Quarter='Q2';
 if month in (7 8 9) then Quarter='Q3';
 if month in (10 11 12) then Quarter='Q4';
run;
proc sql;
create table act_rvp_sum as
select unique
 month,
 Quarter,
 cabernet        as m_cabernet,
 zinfandel       as m_zinfandel,
 merlot          as m_merlot,
 chardonnay      as m_chardonnay,
 sauvignan_blanc as m_sauvignan_blanc,
 sum(cabernet)        as cabernet,
 sum(zinfandel)       as zinfandel,
 sum(merlot)          as merlot,
 sum(chardonnay)      as chardonnay,
 sum(sauvignan_blanc) as sauvignan_blanc
from act_rvp
group by Quarter;
quit; run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvp1 REPLACE;
 RANGE='Scenario3$A77:E77';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvp1 (drop=a b c d e); set tgt_rvp1;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvp1 out=tgt_rvp1; by f1; run;
data tgt_rvp1 (keep=Quarter cabernet); set tgt_rvp1;
product=f1; cabernet=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvp2 REPLACE;
 RANGE='Scenario3$A78:E78';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvp2 (drop=a b c d e); set tgt_rvp2;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvp2 out=tgt_rvp2; by f1; run;
data tgt_rvp2 (keep=Quarter zinfandel); set tgt_rvp2;
product=f1; zinfandel=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvp3 REPLACE;
 RANGE='Scenario3$A79:E79';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvp3 (drop=a b c d e); set tgt_rvp3;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvp3 out=tgt_rvp3; by f1; run;
data tgt_rvp3 (keep=Quarter merlot); set tgt_rvp3;
product=f1; merlot=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvp4 REPLACE;
 RANGE='Scenario3$A80:E80';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvp4 (drop=a b c d e); set tgt_rvp4;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvp4 out=tgt_rvp4; by f1; run;
data tgt_rvp4 (keep=Quarter chardonnay); set tgt_rvp4;
product=f1; chardonnay=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvp5 REPLACE;
 RANGE='Scenario3$A81:E81';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvp5 (drop=a b c d e); set tgt_rvp5;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvp5 out=tgt_rvp5; by f1; run;
data tgt_rvp5 (keep=Quarter sauvignan_blanc); set tgt_rvp5;
product=f1; sauvignan_blanc=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;


data tgt_rvp;
 merge tgt_rvp1 tgt_rvp2 tgt_rvp3 tgt_rvp4 tgt_rvp5;
run;
proc sql;
create table tgt_rvp_sum as
select unique
 Quarter,
 sum(cabernet)      as cabernet,
 sum(zinfandel)       as zinfandel,
 sum(merlot)   as merlot,
 sum(chardonnay) as chardonnay,
 sum(sauvignan_blanc) as sauvignan_blanc
from tgt_rvp
group by Quarter;
quit; run;

proc datasets;
 modify act_rvp_sum;
 rename cabernet      = a_cabernet;
 rename zinfandel       = a_zinfandel;
 rename merlot   = a_merlot;
 rename chardonnay = a_chardonnay;
 rename sauvignan_blanc = a_sauvignan_blanc;
run;

proc datasets;
 modify tgt_rvp_sum;
 rename cabernet      = t_cabernet;
 rename zinfandel       = t_zinfandel;
 rename merlot   = t_merlot;
 rename chardonnay = t_chardonnay;
 rename sauvignan_blanc = t_sauvignan_blanc;
run;

proc sql;
create table products as
select act_rvp_sum.*, 
       tgt_rvp_sum.t_cabernet, tgt_rvp_sum.t_zinfandel, tgt_rvp_sum.t_merlot, tgt_rvp_sum.t_chardonnay, tgt_rvp_sum.t_sauvignan_blanc
from act_rvp_sum left join tgt_rvp_sum
on act_rvp_sum.Quarter = tgt_rvp_sum.Quarter;
quit; run;


data products; set products;
format a_cabernet dollar12.0;
format a_zinfandel dollar12.0;
format a_merlot dollar12.0;
format a_chardonnay dollar12.0;
format a_sauvignan_blanc dollar12.0;

length ev_cabernet $ 12;
length ev_zinfandel $ 12;
length ev_merlot $ 12;
length ev_chardonnay $ 12;
length ev_sauvignan_blanc $ 12;

 /* Evaluation of actual, as a percentage of target */

 pct_cabernet=a_cabernet/t_cabernet;
 if pct_cabernet < .6 then ev_cabernet='Poor';
 else if pct_cabernet < .9 then ev_cabernet='Satisfactory';
 else if pct_cabernet >=.9 then ev_cabernet='Good';

 pct_zinfandel=a_zinfandel/t_zinfandel;
 if pct_zinfandel < .6 then ev_zinfandel='Poor';
 else if pct_zinfandel < .9 then ev_zinfandel='Satisfactory';
 else if pct_zinfandel >=.9 then ev_zinfandel='Good';

 pct_merlot=a_merlot/t_merlot;
 if pct_merlot < .6 then ev_merlot='Poor';
 else if pct_merlot < .9 then ev_merlot='Satisfactory';
 else if pct_merlot >=.9 then ev_merlot='Good';

 pct_chardonnay=a_chardonnay/t_chardonnay;
 if pct_chardonnay < .6 then ev_chardonnay='Poor';
 else if pct_chardonnay < .9 then ev_chardonnay='Satisfactory';
 else if pct_chardonnay >=.9 then ev_chardonnay='Good';

 pct_sauvignan_blanc =a_sauvignan_blanc /t_sauvignan_blanc ;
 if pct_sauvignan_blanc  < .6 then ev_sauvignan_blanc ='Poor';
 else if pct_sauvignan_blanc  < .9 then ev_sauvignan_blanc ='Satisfactory';
 else if pct_sauvignan_blanc  >=.9 then ev_sauvignan_blanc ='Good';

run;

/* This is a missing-value placeholder, to guarantee that
   all the bar charts use the 2 colors in the same way.  */
data fooproducts;

length ev_cabernet $ 12;
length ev_zinfandel $ 12;
length ev_merlot $ 12;
length ev_chardonnay $ 12;
length ev_sauvignan_blanc  $ 12;

a_cabernet=.;
a_zinfandel=.;
a_merlot=.;
a_chardonnay=.;
a_sauvignan_blanc =.;
Quarter='Q1';

ev_cabernet='Poor'; ev_zinfandel='Poor'; ev_merlot='Poor'; ev_chardonnay='Poor';
 ev_sauvignan_blanc ='Poor'; output;

ev_cabernet='Satisfactory'; ev_zinfandel='Satisfactory'; ev_merlot='Satisfactory'; ev_chardonnay='Satisfactory';
 ev_sauvignan_blanc ='Satisfactory'; output;

ev_cabernet='Good'; ev_zinfandel='Good'; ev_merlot='Good'; ev_chardonnay='Good';
 ev_sauvignan_blanc ='Good';  output;

run;
data products; set products fooproducts; run;



/* ------------------------------------------------------------------------------------ */



PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvr1 REPLACE;
 RANGE='Scenario3$A37:N37';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvr1 (drop=a b c d e f g h i j k l m n); set act_rvr1;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvr1; set act_rvr1 (drop=f2); run;
proc transpose data=act_rvr1 out=act_rvr1; by f1; run;
data act_rvr1 (keep=month Quarter namerica); set act_rvr1;
product=f1; namerica=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                               

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvr2 REPLACE;
 RANGE='Scenario3$A38:N38';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvr2 (drop=a b c d e f g h i j k l m n); set act_rvr2;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvr2; set act_rvr2 (drop=f2); run;
proc transpose data=act_rvr2 out=act_rvr2; by f1; run;
data act_rvr2 (keep=month Quarter europe); set act_rvr2;
product=f1; europe=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                               

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvr3 REPLACE;
 RANGE='Scenario3$A39:N39';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvr3 (drop=a b c d e f g h i j k l m n); set act_rvr3;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvr3; set act_rvr3 (drop=f2); run;
proc transpose data=act_rvr3 out=act_rvr3; by f1; run;
data act_rvr3 (keep=month Quarter asia); set act_rvr3;
product=f1; asia=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                               

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvr4 REPLACE;
 RANGE='Scenario3$A40:N40';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvr4 (drop=a b c d e f g h i j k l m n); set act_rvr4;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvr4; set act_rvr4 (drop=f2); run;
proc transpose data=act_rvr4 out=act_rvr4; by f1; run;
data act_rvr4 (keep=month Quarter samerica); set act_rvr4;
product=f1; samerica=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                               

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=act_rvr5 REPLACE;
 RANGE='Scenario3$A41:N41';
 GETNAMES=NO; MIXED=NO; RUN;
data act_rvr5 (drop=a b c d e f g h i j k l m n); set act_rvr5;
 f1=a; f2=b; f3=c; f4=d; f5=e; f6=f; f7=g; f8=h; f9=i; f10=j; f11=k; f12=l; f13=m; f14=n;
 run;
data act_rvr5; set act_rvr5 (drop=f2); run;
proc transpose data=act_rvr5 out=act_rvr5; by f1; run;
data act_rvr5 (keep=month Quarter mideast); set act_rvr5;
product=f1; mideast=col1; Month=0; Month=(scan(_name_,1,'f')-2); run;                               

data act_rvr;
 merge act_rvr1 act_rvr2 act_rvr3 act_rvr4 act_rvr5;
run;
data act_rvr; set act_rvr;
 if month in (1 2 3) then Quarter='Q1';
 if month in (4 5 6) then Quarter='Q2';
 if month in (7 8 9) then Quarter='Q3';
 if month in (10 11 12) then Quarter='Q4';
run;
proc sql;
create table act_rvr_sum as
select unique
 month,
 Quarter,
 namerica as m_namerica,
 europe   as m_europe,
 asia     as m_asia,
 samerica as m_samerica,
 mideast  as m_mideast,
 sum(namerica) as namerica,
 sum(europe)   as europe,
 sum(asia)     as asia,
 sum(samerica) as samerica,
 sum(mideast)  as mideast
from act_rvr
group by Quarter;
quit; run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvr1 REPLACE;
 RANGE='Scenario3$A84:E84';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvr1 (drop=a b c d e); set tgt_rvr1;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvr1 out=tgt_rvr1; by f1; run;
data tgt_rvr1 (keep=Quarter namerica); set tgt_rvr1;
product=f1; namerica=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvr2 REPLACE;
 RANGE='Scenario3$A85:E85';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvr2 (drop=a b c d e); set tgt_rvr2;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvr2 out=tgt_rvr2; by f1; run;
data tgt_rvr2 (keep=Quarter europe); set tgt_rvr2;
product=f1; europe=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvr3 REPLACE;
 RANGE='Scenario3$A86:E86';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvr3 (drop=a b c d e); set tgt_rvr3;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvr3 out=tgt_rvr3; by f1; run;
data tgt_rvr3 (keep=Quarter asia); set tgt_rvr3;
product=f1; asia=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvr4 REPLACE;
 RANGE='Scenario3$A87:E87';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvr4 (drop=a b c d e); set tgt_rvr4;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvr4 out=tgt_rvr4; by f1; run;
data tgt_rvr4 (keep=Quarter samerica); set tgt_rvr4;
product=f1; samerica=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;

PROC IMPORT DATAFILE= "scenario3.xls" DBMS=XLS OUT=tgt_rvr5 REPLACE;
 RANGE='Scenario3$A88:E88';
 GETNAMES=NO; MIXED=NO; RUN;
data tgt_rvr5 (drop=a b c d e); set tgt_rvr5;
 f1=a; f2=b; f3=c; f4=d; f5=e;
 run;
proc transpose data=tgt_rvr5 out=tgt_rvr5; by f1; run;
data tgt_rvr5 (keep=Quarter mideast); set tgt_rvr5;
product=f1; mideast=col1; Quarter='Q'||trim(left((scan(_name_,1,'f')-1))); run;


data tgt_rvr;
 merge tgt_rvr1 tgt_rvr2 tgt_rvr3 tgt_rvr4 tgt_rvr5;
run;
proc sql;
create table tgt_rvr_sum as
select unique
 Quarter,
 sum(namerica)      as namerica,
 sum(europe)       as europe,
 sum(asia)   as asia,
 sum(samerica) as samerica,
 sum(mideast) as mideast
from tgt_rvr
group by Quarter;
quit; run;

proc datasets;
 modify act_rvr_sum;
 rename namerica      = a_namerica;
 rename europe       = a_europe;
 rename asia   = a_asia;
 rename samerica = a_samerica;
 rename mideast = a_mideast;
run;

proc datasets;
 modify tgt_rvr_sum;
 rename namerica      = t_namerica;
 rename europe       = t_europe;
 rename asia   = t_asia;
 rename samerica = t_samerica;
 rename mideast = t_mideast;
run;

data regions;
 merge act_rvr_sum tgt_rvr_sum;
run;

proc sql;
create table regions as
select act_rvr_sum.*, 
       tgt_rvr_sum.t_namerica, tgt_rvr_sum.t_europe, tgt_rvr_sum.t_asia, tgt_rvr_sum.t_samerica, tgt_rvr_sum.t_mideast
from act_rvr_sum left join tgt_rvr_sum
on act_rvr_sum.Quarter = tgt_rvr_sum.Quarter;
quit; run;

data regions; set regions;
format a_namerica dollar12.0;
format a_europe   dollar12.0;
format a_asia     dollar12.0;
format a_samerica dollar12.0;
format a_mideast  dollar12.0;

length ev_namerica $ 12;
length ev_europe $ 12;
length ev_asia $ 12;
length ev_samerica $ 12;
length ev_mideast $ 12;


 pct_namerica=a_namerica/t_namerica;
 if pct_namerica < .6 then ev_namerica='Poor';
 else if pct_namerica < .9 then ev_namerica='Satisfactory';
 else if pct_namerica >=.9 then ev_namerica='Good';

 pct_europe=a_europe/t_europe;
 if pct_europe < .6 then ev_europe='Poor';
 else if pct_europe < .9 then ev_europe='Satisfactory';
 else if pct_europe >=.9 then ev_europe='Good';

 pct_asia=a_asia/t_asia;
 if pct_asia < .6 then ev_asia='Poor';
 else if pct_asia < .9 then ev_asia='Satisfactory';
 else if pct_asia >=.9 then ev_asia='Good';

 pct_samerica=a_samerica/t_samerica;
 if pct_samerica < .6 then ev_samerica='Poor';
 else if pct_samerica < .9 then ev_samerica='Satisfactory';
 else if pct_samerica >=.9 then ev_samerica='Good';

 pct_mideast =a_mideast /t_mideast ;
 if pct_mideast  < .6 then ev_mideast ='Poor';
 else if pct_mideast  < .9 then ev_mideast ='Satisfactory';
 else if pct_mideast  >=.9 then ev_mideast ='Good';

run;

data fooregions;

length ev_namerica $ 12;
length ev_europe $ 12;
length ev_asia $ 12;
length ev_samerica $ 12;
length ev_mideast  $ 12;

a_namerica=.;
a_europe=.;
a_asia=.;
a_samerica=.;
a_mideast =.;
Quarter='Q1';

ev_namerica='Poor'; ev_europe='Poor'; ev_asia='Poor'; ev_samerica='Poor';
 ev_mideast ='Poor'; output;

ev_namerica='Satisfactory'; ev_europe='Satisfactory'; ev_asia='Satisfactory'; ev_samerica='Satisfactory';
 ev_mideast ='Satisfactory'; output;

ev_namerica='Good'; ev_europe='Good'; ev_asia='Good'; ev_samerica='Good';
 ev_mideast ='Good';  output;

run;
data regions; set regions fooregions; run;


/* ------------------------------------------------------------------------------------ */


GOPTIONS DEVICE=gif;
goptions cback=&backcolor;
 
ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm"
(title="Drilldown graphs for Scenario 3 (Sales Dashboard)")
style=minimal
;
goptions border;

goptions gunit=pct htitle=5 ftitle="arial/bo" htext=3.25 ftext="arial/bo";

%let green=cxc2e699;
%let pink=cxfa9fb5;
%let red=cxff0000;
%let outgray=gray;

pattern1 v=s c=&green;
pattern2 v=s c=&red;
pattern3 v=s c=&pink;

legend1 
/*
 position=(middle right outside) 
*/
 position=(top) 
 across=3 label=none shape=bar(1.5,2.25) 
 order=('Good' 'Satisfactory' 'Poor') 
;

axis1 label=none major=(number=5) minor=none offset=(0,0);
axis2 offset=(4,4);

title "Revenue ($)";
title2 "&titl2txt";
proc gchart data=metrics;
format m_revenue dollar10.0;
vbar month / discrete group=Quarter subgroup=ev_revenue nozero type=sum sumvar=m_revenue coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot16' des=''; run;

title "Profit ($)";
title2 "&titl2txt";
proc gchart data=metrics;
format m_profit dollar10.0;
vbar month / discrete group=Quarter subgroup=ev_profit nozero type=sum sumvar=m_profit coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot17' des=''; run;

title "Average Order Size";
title2 "&titl2txt";
proc gchart data=metrics;
format m_order_size comma5.0;
vbar month / discrete group=Quarter subgroup=ev_order_size nozero type=sum sumvar=m_order_size coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot2' des=''; run;

title "New Customers";
title2 "&titl2txt";
proc gchart data=metrics;
format m_new_cust comma5.0;
vbar month / discrete group=Quarter subgroup=ev_new_cust nozero type=sum sumvar=m_new_cust coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot3' des=''; run;

axis1 label=none order=(0 to 5 by 1) minor=none offset=(0,0);
title "Customer Satisfaction";
title2 "&titl2txt";
proc gchart data=metrics;
format m_satisfaction comma5.0;
vbar month / discrete group=Quarter subgroup=ev_satisfaction nozero type=sum sumvar=m_satisfaction coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot5' des=''; run;

axis1 label=none minor=none offset=(0,0) order=(0 to .25 by .05);
title "Market Share";
title2 "&titl2txt";
proc gchart data=metrics;
format m_market_share percent7.0;
vbar month / discrete group=Quarter subgroup=ev_market_share nozero type=sum sumvar=m_market_share coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot1' des=''; run;

axis1 label=none minor=none offset=(0,0) order=(0 to 1 by .2);
title "On Time Delivery";
title2 "&titl2txt";
proc gchart data=metrics;
format m_on_time percent7.0;
vbar month / discrete group=Quarter subgroup=ev_on_time nozero type=sum sumvar=m_on_time coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot4' des=''; run;




axis1 label=none major=(number=5) minor=none offset=(0,0);
axis2 offset=(4,4);


title "Cabernet";
title2 "&titl2txt";
proc gchart data=products;
format m_cabernet dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_cabernet nozero type=sum sumvar=m_cabernet coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot7' des=''; run;

title "Zinfandel";
title2 "&titl2txt";
proc gchart data=products;
format m_zinfandel dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_zinfandel nozero type=sum sumvar=m_zinfandel coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot10' des=''; run;

title "Merlot";
title2 "&titl2txt";
proc gchart data=products;
format m_merlot dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_merlot nozero type=sum sumvar=m_merlot coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot8' des=''; run;

title "Chardonnay";
title2 "&titl2txt";
proc gchart data=products;
format m_chardonnay dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_chardonnay nozero type=sum sumvar=m_chardonnay coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot6' des=''; run;

title "Sauvignan Blanc";
title2 "&titl2txt";
proc gchart data=products;
format m_sauvignan_blanc dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_sauvignan_blanc nozero type=sum sumvar=m_sauvignan_blanc coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot9' des=''; run;




title "North America";
title2 "&titl2txt";
proc gchart data=regions;
format m_namerica dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_namerica nozero type=sum sumvar=m_namerica coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot11' des=''; run;

title "Europe";
title2 "&titl2txt";
proc gchart data=regions;
format m_europe dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_europe nozero type=sum sumvar=m_europe coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot13' des=''; run;

title "Asia";
title2 "&titl2txt";
proc gchart data=regions;
format m_asia dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_asia nozero type=sum sumvar=m_asia coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot12' des=''; run;

title "South America";
title2 "&titl2txt";
proc gchart data=regions;
format m_samerica dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_samerica nozero type=sum sumvar=m_samerica coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot15' des=''; run;

title "Middle East";
title2 "&titl2txt";
proc gchart data=regions;
format m_mideast dollar12.0;
vbar month / discrete group=Quarter subgroup=ev_mideast nozero type=sum sumvar=m_mideast coutline=&outgray raxis=axis1 maxis=axis2 
 legend=legend1 width=6 space=3 gspace=1 autoref cref=&refcolor clipref noframe name='plot14' des=''; run;

quit;
ODS HTML CLOSE;
ODS LISTING;