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

/* This macro variable is set via durham_dept.sas */
/*
%let department=PUBLIC_WORKS;
*/



filename myfile 'durham_salary_2008.csv';
data durham_data;
infile myfile firstobs=2 lrecl=100 pad termstr=CRLF;
  input whole_line $ 1-100;
run;

data durham_data (drop=whole_line second_half salary_char); set durham_data;
  length salary_char $50;
  salary_char=substr(whole_line,2,index(whole_line,'",')-2);
  salary=input(salary_char, dollar20.2);
  length second_half $100;
  second_half=substr(whole_line,index(whole_line,'",')+2);
  length job_title $100;
  job_title=scan(second_half,1,',');
  length anchor $100;
  anchor = '<a name="' || translate(trim(left(job_title)),'___','/ &') || '"></a>';
  length department_name $100;
  department_name=scan(second_half,2,',');
  if salary ne . and translate(trim(left(department_name)),'___','/ &')="&department" then output;
run;

data original_data; set durham_data;
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 jobs as 
select unique job_title 
from durham_data;
quit; run;
data jobs; set jobs;
length idnum $10;
idnum=trim(left(_n_));
run;
proc sql;
create table durham_data as 
select durham_data.*, jobs.idnum
from durham_data left join jobs
on durham_data.job_title = jobs.job_title;
quit; run;

proc sql;
create table durham_data as 
select unique job_title, idnum, sum(salary) as salary_sum, avg(salary) as salary_avg, count(*) as count
from durham_data
group by job_title, idnum;
quit; run;



data durham_data; set durham_data;
length myhtml $1000;
myhtml='title='||
quote(
'Job Title: '||trim(left(job_title))||'0D'x||
'Salaried Employee Count: '||trim(left(count))||'0D'x||
'Average Salary: '||trim(left(put(salary_avg,dollar20.0)))||'0D'x||
'Total Salary: '||trim(left(put(salary_sum,dollar20.0)))
)
||' '||
 'href="#'||translate(trim(left(job_title)),'___','/ &')||'"';
/*
'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 salary, and in the gmap I'll color them by avg salary */
libname here '.';
proc datasets lib=here nolist nowarn; delete durhammap; run;
%include 'treemap_inc.sas';
%mini_tree(durham_data,idnum,salary_sum,here.durhammap,0,0,135,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.durhammap,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.*, jobs.job_title
from annolabel left join jobs
on annolabel.text = jobs.idnum;
quit; run;
data annolabel; 
 length text $100;
 set annolabel;
 text=job_title;
 size=1.5;
run;



 GOPTIONS DEVICE=png;
 goptions xpixels=900 ypixels=600;
 
 ODS LISTING CLOSE;
 ODS HTML path=odsout body="&name._&department..htm" (title="City of Durham &department Salary Data (2008)") style=minimal;

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

 title1 "City of Durham &department Salary Data (2008)";
 title2 "(mouse over tiles to see Job Title & Salary Info)";
 title3 a=-90 h=5pct " ";
 footnote "size = total salary      color = average salary";
 footnote2 h=.5 " ";


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

proc format;
  value valfmt
  1='<=$25k'
  2='$25-30k'
  3='$30-35k'
  4='$35-40k'
  5='$40-50k'
  6='$50-75k'
  7='$75-100k'
  8='>$100k'
 ;
data durham_data; set durham_data;
 format rangeval valfmt.;
      if salary_avg<= 25000 then rangeval=1;
 else if salary_avg<= 30000 then rangeval=2;
 else if salary_avg<= 35000 then rangeval=3;
 else if salary_avg<= 40000 then rangeval=4;
 else if salary_avg<= 50000 then rangeval=5;
 else if salary_avg<= 75000 then rangeval=6;
 else if salary_avg<=100000 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=durham_data map=here.durhammap; 
 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._&department"; run;


/*
 proc sort data=durham_data;
 by descending salary_sum;
 run;
 proc print data=durham_data; 
 format salary_sum salary_avg dollar20.0;
 var job_title salary_sum salary_avg;
 sum salary_sum;
 run;
*/

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

 quit;
 ODS HTML CLOSE;
 ODS LISTING;
 
data original_data;set original_data;
anchor = '<a name="' || translate(trim(left(job_title)),'___','/ &') || '"></a>';
salary_fmt=put(salary,dollar20.0);
run;
proc sort data=original_data out=original_data;
by job_title descending salary;
run;
filename mytable "./&name._&department..htm" mod;
data _null_; set original_data end=last;
 by job_title;
 file mytable;
 if first.job_title then do;
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put anchor;
  put "</br>";
  put "</br>";
  put "<table border='1'>";
  put "<th width='300px'>job_title</th>";
  put "<th width='125px'>Salary</th>";
 end;
 put "<tr>";
 put "<td>";
 put job_title;
 put "</td>";
 put "<td align=right>";
 put salary_fmt;
 put "</td>";
 put "</tr>";
 if last.job_title then do;
  put "</table>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</br>";
  put "</body>";
  put "</html>";
 end;

run;
quit;