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

PROC IMPORT OUT=cary_data DATAFILE="cary_wages.xls" DBMS=EXCEL REPLACE;
     GETNAMES=YES;
RUN;

/*
data cary_data; set cary_data;
if index(department,'PW/UT')=1 then department='PUBLIC WORKS / UTILITIES';
if index(department,'P&R')=1 then department='PARKS & REC';
run;
*/

/*
Unfortunately, department names are too long to use for the Tree Map id's, so I assign a unique id number
and use that instead...
*/
proc sql;
create table departments as 
select unique department 
from cary_data;
quit; run;
data departments; set departments;
length idnum $10;
idnum=trim(left(_n_));
run;
proc sql;
create table cary_data as 
select cary_data.*, departments.idnum
from cary_data left join departments
on cary_data.department = departments.department;
quit; run;

proc sql;
create table cary_data as 
select unique department, idnum, sum(hourly_rate) as hourly_rate_sum, avg(hourly_rate) as hourly_rate_avg, count(*) as count
from cary_data
group by department, idnum;
quit; run;



data cary_data; set cary_data;
length myhtml $1000;
myhtml='title='||
quote(
'Department: '||trim(left(department))||'0D'x||
'Employee Count: '||trim(left(count))||'0D'x||
'Average hourly_rate: '||trim(left(put(hourly_rate_avg,dollar20.0)))||'0D'x||
'Total of all employees: '||trim(left(put(hourly_rate_sum,dollar20.0)))
)
||' '||
'href="cary_'||translate(trim(left(department)),'____','/ -&')||'.htm"'
;
/*
'href="http://www.wral.com/news/public_records/page/1282943"'
*/
run;



/* Use the macro to creat the sas/graph map data set for this tree map */
/* I'm sizing the blocks based on the total hourly_rate, and in the gmap I'll color them by avg hourly_rate */
libname here '.';
proc datasets lib=here nolist nowarn; delete carymap; run;
%include 'treemap_inc.sas';
%mini_tree(cary_data,idnum,hourly_rate_sum,here.carymap,0,0,160,100);
/*
*/

/* 100x100 is square - I do 180x100 to fit the available space better */


/* Create annotate data set for state abbreviations */
%include 'treeanno2_inc.sas';
%treelabel2(here.carymap,idnum,black,annolabel);
/* 
Now, merge the actual department names back in, and use them as the annotated
text label, instead of the idnum.
*/
proc sql;
create table annolabel as 
select unique annolabel.*, departments.department
from annolabel left join departments
on annolabel.text = departments.idnum;
quit; run;
data annolabel; 
 length text $100;
 set annolabel;
 text=department;
 size=1.8;
run;



 GOPTIONS DEVICE=png;
 goptions xpixels=900 ypixels=600;
 
 ODS LISTING CLOSE;
 ODS HTML path=odsout body="&name..htm" (title="City of Cary Hourly-Wage-Rate Data (2008)") style=minimal;

 
 goptions gunit=pct ftitle="arial/bold" ftext="arial" htitle=4 htext=3;

 title1 "City of Cary Hourly-Wage-Rate Data (2008)";
 title2 "(mouse over tiles to see Department Name & hourly_rate Info)";
 title3 a=-90 h=5pct " ";
 footnote "size = total (sum of) hourly wages      color = average hourly wage";
 footnote2 h=.5 " ";


legend1 position=(left middle) across=1 shape=bar(.2in,.2in)
  label=(position=top 'Avg hourly_rate') value=(h=2.75);

proc format;
  value valfmt
  1='<=$12.50'
  2='$12.50-15.00'
  3='$15.00-17.50'
  4='$17.50-20.00'
  5='$20.00-25.00'
  6='$25.00-37.50'
  7='$37.50-50.00'
  8='>$50.00'
 ;
data cary_data; set cary_data;
 format rangeval valfmt.;
      if hourly_rate_avg<=12.50  then rangeval=1;
 else if hourly_rate_avg<=15.00  then rangeval=2;
 else if hourly_rate_avg<=17.50  then rangeval=3;
 else if hourly_rate_avg<=20.00  then rangeval=4;
 else if hourly_rate_avg<=25.00  then rangeval=5;
 else if hourly_rate_avg<=37.50  then rangeval=6;
 else if hourly_rate_avg<=50.000 then rangeval=7;
 else rangeval=8;
run;

pattern1 v=solid color=cx1a9850;
pattern2 v=solid color=cx66bd63;
pattern3 v=solid color=cxa6d96a;
pattern4 v=solid color=cxd9ef8b;
pattern5 v=solid color=cxfee08b;
pattern6 v=solid color=cxfdae61;
pattern7 v=solid color=cxf46d43;
pattern8 v=solid color=cxd73027;



 proc gmap data=cary_data map=here.carymap; 
 id idnum; 
 choro rangeval / discrete midpoints = 1 2 3 4 5 6 7 8
 anno=annolabel
 html=myhtml coutline=grayaa woutline=2 legend=legend1
 des="" name="&name"; run;


 proc sort data=cary_data;
 by descending hourly_rate_sum;
 run;
 title;
 footnote;
 proc print data=cary_data label; 
 format hourly_rate_sum hourly_rate_avg dollar20.0;
 format count comma10.0;
 label count='Employees';
 var department hourly_rate_sum count hourly_rate_avg;
 sum hourly_rate_sum count;
 run;

/*
proc print data=annolabel;
run;
*/

 quit;
 ODS HTML CLOSE;
 ODS LISTING;