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

/*
http://www.wral.com/news/public_records/?show_all=1
http://www.wral.com/news/public_records/page/1261595/
http://www.wral.com/news/public_records/page/1281122/
*/


filename myfile 'wake_schools_2008.csv';
data wake_data;
infile myfile firstobs=2 lrecl=100 pad termstr=CRLF;
  input whole_line $ 1-100;
run;
data wake_data (drop=whole_line second_half salary_char); set wake_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 school_name $100;
  school_name=scan(second_half,2,',');
  /* idnum and school_name are interchangeable (use idnum when you need a short id */
  length idnum $5;
  idnum=trim(left(scan(school_name,2,'-')));
  school_name=substr(school_name,1,length(school_name)-6);
run;


/* Convert salary from character to numeric */
data wake_data; set wake_data;
 if index(school_name,'School') ne 0 then output;
run;
data wake_data; set wake_data;
length school_type $50;
if index(school_name,'High School') ne 0 then school_type='High Schools';
if index(school_name,'Middle School') ne 0 then school_type='Middle Schools';
if index(school_name,'Elementary School') ne 0 then school_type='Elementary Schools';
if school_type ne '' and
 school_name not in ('Elementary School Education' 'High School Education' 'Middle School Programs')
 then output;
run;

data wake_data; set wake_data;
length drillname $200;
drillname="http://www.google.com/search?&q=nc+wake+county+"||trim(left(school_name));
run;

proc sql;
create table mydata as
select unique school_type, school_name, idnum, job_title, drillname,
   count(*) as count,
   sum(salary) format=dollar20.0 as salary_sum,
   avg(salary) format=dollar20.0 as salary_avg
from wake_data
group by school_type, school_name, idnum, job_title;
quit; run;

data mydata; set mydata;
length uniquenum $10;
uniquenum=trim(left(_n_));
length myhtml $1000;
myhtml='title='||
quote(
trim(left(school_name))||'0D'x||
trim(left(job_title))||'0D'x||
'Salaried employees = '||trim(left(count))||'0D'x||
'Salary Average  : '||trim(left(put(salary_avg,dollar20.0)))||'0D'x||
'Salary Sum  : '||trim(left(put(salary_sum,dollar20.0)))
)
||' '||
 'href="#'||translate(trim(left(school_name)),'_',' ')||'"';
/*
' href='||
quote(drillname)
;
*/
length anchor $100;
anchor = '<a name="' || translate(trim(left(school_name)),'_',' ') || '"></a>';
run;



data mydata; set mydata (where=(school_type='Elementary Schools'));
run;

/* Creat the sas/graph map data set */
libname here '.';
/*
proc datasets lib=here nolist nowarn; delete wake_elem; run;
%include 'treemap_inc.sas';
%treemac(mydata,school_name,uniquenum,salary_sum,here.wake_elem,0,0,130,100);
*/


/* Create annotate data sets (outline & labels for school_type) */
%include 'treeanno_inc.sas';
%treelabel(here.wake_elem,school_name,black,annofram,annolabel);

/* make the black-colored border around the framed-in school_type
   a little thicker. */
data annofram; set annofram;
 size=.6;
run;

/* Make the label font a little larger */
data annolabel; set annolabel;
 size=1.5;
 text=trim(left(substr(text,1,index(text,'Elementary School')-1)));
run;

data tree_anno;
 set annofram annolabel;
run;


 GOPTIONS DEVICE=gif;
 ODS LISTING CLOSE;
 ODS HTML path=odsout body="&name..htm" (title="Wake County NC Elementary Schools - Salary Data") style=minimal;

 goptions noborder;
 goptions gunit=pct ftitle="arial" ftext="arial" htitle=5.5 htext=3;
 goptions cback=white;

 title "Wake County NC Elementary Schools - Salary Data";
 title2 "(mouse over tiles to see Job Title & Salary Info)";
 footnote "size = total salary      color = average salary";
 footnote2 h=.5 " ";

legend1 position=(right 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 mydata; set mydata;
 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=mydata map=here.wake_elem anno=tree_anno; 
 id school_name uniquenum; 
/*
 choro school_type / 
 choro salary_avg / 
*/
 choro rangeval / discrete midpoints = 1 2 3 4 5 6 7 8
 coutline=gray88
 woutline=1 
 legend=legend1
 html=myhtml 
 des="" name="&name"; run;

title;
footnote;

proc sort data=mydata out=mydata;
by school_name descending salary_avg;
run;

/*
proc print data=mydata noobs;
by school_name;
var job_title salary_avg count salary_sum;
sum salary_sum;
run;
*/

 quit;
 ODS HTML CLOSE;
 ODS LISTING;
 

filename mytable "./&name..htm" mod;
data _null_; set mydata end=last;
 by school_name;
 file mytable;
 if first.school_name then do;
  put "</br>";
  put "</br>";
  put anchor;
  put "</br>";
 put "<h3>";
 put school_name;
 put "</h3>";
  put "</br>";
  put "<table border='1'>";
  put "<th width='300px'>job_title</th>";
  put "<th width='125px'>Salary Avg</th>";
  put "<th width='20px'>Count</th>";
  put "<th width='150px'>Salary Sum</th>";
 end;
 put "<tr>";
 put "<td>";
 put job_title;
 put "</td>";
 put "<td align=right>";
 put salary_avg;
 put "<td align=right>";
 put count;
 put "</td>";
 put "<td align=right>";
 put salary_sum;
 put "</td>";
 put "</tr>";
 if last.school_name 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 "</body>";
  put "</html>";
 end;

run;
quit;