%let name=us_legislators_age;

/* %let rc=%sysfunc(dlgcdir('c:\someplace\public_html')); */
filename odsout '.';

/*
Inspired by:
https://www.reddit.com/r/dataisbeautiful/comments/9ottb3/mean_age_of_united_states_congressional/

Using data from:
https://github.com/unitedstates/congress-legislators
https://theunitedstates.io/congress-legislators/legislators-current.csv

Saved as legislators-current.xls
*/

%let snapshot=17sep2018;

PROC IMPORT OUT=my_data 
 DATAFILE="legislators-current.xls" 
 DBMS=XLS REPLACE;
SHEET="legislators-current";
GETNAMES=YES;
RUN;

proc sql noprint;
create table my_data as
select * from my_data
where state in (select unique statecode from mapsgfk.us_states_attr where statecode^='DC');
quit; run;

data my_data; set my_data;
age=.;
age=put(yrdif(birthday,"&snapshot"d,'age'),comma5.1);
length job $50;
if type='rep' then job='House of Representatives';
if type='sen' then job='Senate';
length my_html $300;
my_html=
 'title='||quote(
  trim(left(fipnamel(stfips(state))))||'0d'x||
  '------------------'||'0d'x||
  trim(left(job))||'0d'x||
  trim(left(full_name))||'0d'x||
  'Age: '||trim(left(age)))||
 ' href='||quote(trim(left(url)));
run;

proc sql noprint;
create table avg_age as
select unique state, avg(age) as avg_age
from my_data
group by state 
order by avg_age;
quit; run;
data avg_age; set avg_age;
plot_order=_n_;
length avg_html $300;
avg_html=
 'title='||quote(
  trim(left(fipnamel(stfips(state))))||'0d'x||
  'Average age of Legislators: '||trim(left(put(avg_age,comma8.2))));
run;

data control; set avg_age (rename=(plot_order=start state=label));
fmtname='statefmt';
type='N';
run;
proc format lib=work cntlin=control;
run;


proc sql noprint;
create table my_data as
select unique my_data.*, avg_age.plot_order
from my_data left join avg_age
on my_data.state=avg_age.state;
quit; run;


goptions device=png;
goptions xpixels=1000 ypixels=600;
 
ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" 
 (title="US Legislators Age") 
 style=htmlblue;

goptions gunit=pct ftitle='albany amt/bold' ftext='albany amt' htitle=18pt htext=11pt;
goptions ctext=gray33;

title1 ls=1.5 "US Legislator Average Age by State";

axis1 label=('Age') order=(0 to 100 by 10) 
 major=none minor=none style=0 offset=(0,0);

axis2 label=none value=(angle=90 font='lucida sans typewriter' h=10pt) offset=(1.5,1.5);

pattern1 v=s c=cx02a896;

ods html anchor="bar";
proc gchart data=avg_age;
note 
 move=(37.7,91)
 link='https://github.com/unitedstates/congress-legislators'
 c=gray "Data source: github (&snapshot snapshot)";
format plot_order statefmt.;
vbar plot_order / discrete
 type=sum sumvar=avg_age
 raxis=axis1 maxis=axis2 noframe
 width=2.1 space=0 coutline=grayea
 autoref cref=gray11 lref=33 frontref
 html=avg_html
 des='' name="&name._bar";
run;


title1 ls=1.5 "US Legislator Age Comparison by State";

symbol1 value=circle height=2.0 interpol=none color=A0000ff33;

axis3 label=('Age') order=(0 to 100 by 10) value=(justify=center)
 major=none minor=none style=0 offset=(0,0);

axis4 label=('States sorted by average age') 
 value=(angle=90 font='lucida sans typewriter' h=10pt) order=(1 to 50 by 1) 
 major=none minor=none offset=(0,0);

ods html anchor="scatter";
proc gplot data=my_data;
format plot_order statefmt.;
note 
 move=(36,91)
 link='https://github.com/unitedstates/congress-legislators'
 c=gray "Data source: github (&snapshot snapshot)";
plot age*plot_order=1 / 
 vaxis=axis3 haxis=axis4 noframe
 autovref cvref=gray77 lvref=33
 href=(1 5 10 15 20 25 30 35 40 45 50) chref=gray77 lhref=33
 html=my_html
 des='' name="&name._scatter";
plot2 age*plot_order=1 /
 vaxis=axis3
 ;
run;


data anno_avg; set avg_age;
xsys='2'; ysys='2'; hsys='3'; when='b';
x=plot_order; y=avg_age;
color='hotpink'; size=0.7;
if _n_=1 then function='move'; 
else function='draw'; 
run;

axis4 label=('States sorted by average age (line represents average age)') 
 value=(angle=90 font='lucida sans typewriter' h=10pt) order=(1 to 50 by 1) 
 major=none minor=none offset=(0,0);

ods html anchor="line";
proc gplot data=my_data;
format plot_order statefmt.;
note
 move=(36,91)
 link='https://github.com/unitedstates/congress-legislators'
 c=gray "Data source: github (&snapshot snapshot)";
plot age*plot_order=1 /
 vaxis=axis3 haxis=axis4 noframe
 autovref cvref=gray77 lvref=33
 href=(1 5 10 15 20 25 30 35 40 45 50) chref=gray77 lhref=33
 anno=anno_avg
 html=my_html
 des='' name="&name._line";
plot2 age*plot_order=1 /
 vaxis=axis3
 ;
run;



title1 ls=1.5 "US Legislator Age Comparison by State";

symbol1 value=circle height=2.0 interpol=none color=A0000ff66;
symbol2 value=none interpol=boxcti color=cxED9121;
symbol2 value=none interpol=boxcti color=cxc51b8a;

axis5 label=('Age') order=(0 to 100 by 10) value=(justify=center)
 major=none minor=none style=0 offset=(0,0);

axis6 label=('States sorted by average age (line represents average age) with boxplot overlaid') 
 value=(angle=90 font='lucida sans typewriter' h=10pt) order=(1 to 50 by 1)
 major=none minor=none offset=(0,0);

ods html anchor="box";
proc gplot data=my_data;
format plot_order statefmt.;
note
 move=(36,91)
 link='https://github.com/unitedstates/congress-legislators'
 c=gray "Data source: github (&snapshot snapshot)";
plot age*plot_order=1 /
 vaxis=axis5 haxis=axis6 noframe
 autovref cvref=gray77 lvref=33
 href=(1 5 10 15 20 25 30 35 40 45 50) chref=gray77 lhref=33
 anno=anno_avg
 html=my_html
 des='' name="&name._box";
plot2 age*plot_order=2 /
 vaxis=axis3
 ;
run;


data avg_age; set avg_age;
statecode=state;
run;

/*
pattern1 v=s c=cxfeebe2;
pattern2 v=s c=cxfbb4b9;
pattern3 v=s c=cxf768a1;
pattern4 v=s c=cxc51b8a;
pattern5 v=s c=cx7a0177;
*/

pattern1 v=s c=cxfeebe2;
pattern2 v=s c=cxfbb4b9;
pattern3 v=s c=cxf768a1;
pattern4 v=s c=cxae017e;

legend1 label=('Average age:') shape=bar(.15in,.15in);

goptions xpixels=800 ypixels=600;
ods html anchor="map";
proc gmap data=avg_age map=mapsgfk.us all;
note 
 move=(32,91)
 link='https://github.com/unitedstates/congress-legislators'
 c=gray "Data source: github (&snapshot snapshot)";
format avg_age comma8.1;
id statecode;
choro avg_age / levels=4 midpoints=old range
 legend=legend1
 html=avg_html
 des='' name="&name._map";
run;

quit;
ODS HTML CLOSE;
ODS LISTING;