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

/*
Set your current-working-directory (to read/write files), if you need to ...
%let rc=%sysfunc(dlgcdir('c:\someplace\public_html'));
*/
filename odsout '.';

/*
Plotting data from:
https://lendedu.com/student-loan-debt-by-school-by-state-2019/
*/

filename st_file 'us_student_debt_by_state.csv';
proc import datafile=st_file out=state_data dbms=csv replace;
getnames=yes;
guessingrows=max;
run;

/* Data imports as character. Chreate a numeric varible from it. */
data state_data; 
length state_name $100;
set state_data (rename=(state=state_name));
label Avg_Debt_Per_Borrower='Average Student Loan Debt per Borrower';
format Avg_Debt_Per_Borrower dollar10.0;
Avg_Debt_Per_Borrower=.;
Avg_Debt_Per_Borrower=input(Average_Student_Loan_Debt_Per_B,dollar10.0);
if state_name='Virigina' then state_name='Virginia';
if index(state_name,'Minnesota')^=0 then state_name='Minnesota';
if state_name='Washington D.C.' then state_name='District of Columbia';
run;

/* merge in the state abbreviations, to use as html anchor drilldown */
proc sql noprint;
create table state_data as
select unique state_data.*, us_states_attr.statecode
from state_data left join mapsgfk.us_states_attr
on state_data.state_name=us_states_attr.idname;
quit; run;

data state_data; set state_data;
length my_html $300;
my_html=
 'title='||quote(
  trim(left(state_name))||'0d'x||
  'Average debt per borrower = '||trim(left(put(Avg_Debt_Per_Borrower,dollar10.0))))||
 'href=#'||trim(left(statecode));
run;

filename highfile 'us_student_loan_debt_highest.csv';
proc import datafile=highfile out=school_highest dbms=csv replace;
getnames=yes;
guessingrows=max;
run;
data school_highest; set school_highest (where=(Institution_Name^='Removed due to discrepancy in reporting'));;
length category $100;
category='#'||trim(left(rank))||" of nation's 250 highest";
state_name=fipnamel(stfips(state));
format avg_debt_per_borrower dollar10.0;
avg_debt_per_borrower=.;
avg_debt_per_borrower=input(Average_Debt_Per_Borrower,dollar10.0);
run;

filename lowfile 'us_student_loan_debt_lowest.csv';
proc import datafile=lowfile out=school_lowest dbms=csv replace;
getnames=yes;
guessingrows=max;
run;
data school_lowest; set school_lowest (where=(Institution_Name^='Removed due to discrepancy in reporting'));;
length category $100;
category='#'||trim(left(rank))||" of nation's 250 lowest";
state_name=fipnamel(stfips(state));
format avg_debt_per_borrower dollar10.0;
avg_debt_per_borrower=.;
avg_debt_per_borrower=input(Average_Debt_Per_Borrower,dollar10.0);
run;


data school_all; 
length institution_name $150;
set school_highest school_lowest;
run;

data maplabel; set mapsgfk.uscenter;
original_order=_n_;
if statecode='HI' then do;
 x=x-.01;
 y=y-.01;
 end;
run;

/* merge in the mouse-over and drilldown from the data */

proc sql noprint;
create table maplabel as
select unique maplabel.*, state_data.my_html as html
from maplabel left join state_data
on maplabel.statecode=state_data.statecode;
quit; run;
proc sort data=maplabel out=maplabel;
by original_order;
run;

data maplabel; set maplabel;
length function $8;
xsys='2'; ysys='2'; hsys='3'; when='a'; 
function='label'; size=.; style='albany amt'; position='5'; color='blue';
text=trim(left(fipstate(state))); 
retain flag 0;
if ocean='Y' then do;                          
 text='a0'x||trim(left(text));
 position='6'; output;    
 function='move';                                                      
 flag=1;
 end;
else if flag=1 then do;                                                                   
 function='draw'; size=.25;
 flag=0;
 end;
output;
run;

data my_map; set mapsgfk.us;
 state_name=fipnamel(state);
run;

data anno_cap;
xsys='3'; ysys='3'; hsys='3'; when='a';
x=80; y=85;
function='label'; position='5'; color='gray44'; size=24; 
style='webdings'; text='EC'x;
run;


goptions device=png;
goptions border;

 
ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm"
 (title="US Student Loan Debt - 2019") 
 style=htmlblue  options(pagebreak='no');

goptions gunit=pct htitle=4.4 htext=2.0 ftitle="albany amt/bold" ftext="albany amt";
goptions ctext=gray33;

pattern1 v=s c=cxfeedde;
pattern2 v=s c=cxfdbe85;
pattern3 v=s c=cxfd8d3c;
pattern4 v=s c=cxe6550d;
pattern5 v=s c=cxa63603;

legend1 label=none shape=bar(.15in,.15in);

title1 ls=1.5 "Average Student Loan Debt per Borrower (2019)";
title2 a=90 h=1 ' ';
title3 a=-90 h=1 ' ';

proc gmap data=state_data map=my_map annotate=maplabel all; 
id state_name; 
choro Avg_Debt_Per_Borrower / levels=5 legend=legend1
 coutline=gray33
 cdefault=red 
 anno=anno_cap
 html=my_html
 des='' name="&name"; 
run;


proc sort data=school_all out=school_all;
by state state_name descending avg_debt_per_borrower;
run;

ods html anchor='#byval(state)';

options nobyline;
title1 h=12pt " ";
title2 h=12pt "#byval(state_name)";
proc print data=school_all label noobs 
 style(data)={font_size=11pt}
 style(header)={font_size=11pt};
by state state_name;
label category='Among the 250 Highest/Lowest in the US';
label Institution_Name='Institution Name';
label avg_debt_per_borrower='Average Debt per Borrower';
var category Institution_Name avg_debt_per_borrower;
run;

quit;
ODS HTML CLOSE;
ODS LISTING;