%let name=state_fed_revenue; filename odsout '.'; /* http://www.pewtrusts.org/en/multimedia/data-visualizations/2014/fiscal-50#ind1 */ PROC IMPORT OUT=my_data DATAFILE="FederalRevenueData.xls" DBMS=XLS REPLACE; RANGE="Federal Share of State Revenue$A6:Q57"; GETNAMES=YES; RUN; data my_data; set my_data (where=(State^='50-state share')); run; proc transpose data=my_data out=my_data (rename=(state=state_name col1=percent_revenue)); by state notsorted; run; data my_data (keep = state_name fiscal_year percent_revenue my_html); set my_data; fiscal_year=.; fiscal_year=substr(_name_,4,4); length my_html $300; my_html='title='||quote( trim(left(state_name))||'0d'x|| trim(left(put(percent_revenue,percentn7.1)))); run; data data_2015; set my_data (where=(fiscal_year=2015)); run; /* Determine quintiles, to color map & graph the same */ proc rank data=data_2015 out=data_2015 groups=5 ties=low; var percent_revenue; ranks quintile_var_2015; run; /* merge the 2015 quintile back into all the years, to color the lines */ proc sql noprint; create table my_data as select unique my_data.*, data_2015.quintile_var_2015 from my_data left join data_2015 on my_data.state_name=data_2015.state_name order by state_name, fiscal_year; quit; run; /* Insert 'missing' values between each state, so gplot won't connect the state lines */ data plot_data; set my_data; by state_name; output; if last.state_name then do; percent_revenue=.; fiscal_year=.; output; end; run; data my_map; set mapsgfk.us; state_name=fipnamel(state); run; goptions device=png; goptions noborder; ODS LISTING CLOSE; ODS html path=odsout body="&name..htm" (title="Federal Share of State Revenue") style=htmlblue; goptions gunit=pct ftitle='albany amt/bold' ftext='albany amt' htitle=18pt htext=10pt; goptions ctext=gray33; footnote link='http://www.pewtrusts.org/en/multimedia/data-visualizations/2014/fiscal-50#ind1' c=gray 'Data source: The PEW Charitable Trusts study of US Census Bureau data'; %let c5=cxd7191c; %let c4=cxfdae61; %let c3=cxffffbf; %let c2=cxabd9e9; %let c1=cx2c7bb6; pattern1 v=s c=&c1; pattern2 v=s c=&c2; pattern3 v=s c=&c3; pattern4 v=s c=&c4; pattern5 v=s c=&c5; legend1 label=none position=(bottom right) mode=share across=1 shape=bar(.15in,.15in) order=descending offset=(0,17); title1 ls=1.5 "Percentage of State Revenue from Federal Funds, FY 2015"; ods html anchor='map'; goptions xpixels=800 ypixels=600; proc gmap data=data_2015 map=my_map all; format percent_revenue percentn7.1; id state_name; /* choro quintile_var_2015 / discrete */ /* Using gmap levels=5 for quintiles, so it will print the ranges in the legend */ choro percent_revenue / levels=5 legend=legend1 coutline=gray99 html=my_html des='' name="&name"; run; axis1 label=none value=(justify=right) offset=(1.2,1.2); axis2 label=none style=0 major=none minor=none; axis3 label=none value=none; ods html anchor='bar'; goptions xpixels=800 ypixels=720; proc gchart data=data_2015; format percent_revenue percentn7.0; hbar state_name / type=sum sumvar=percent_revenue descending nostats subgroup=quintile_var_2015 nolegend maxis=axis1 raxis=axis2 gaxis=axis3 noframe space=0 coutline=gray99 cref=gray55 lref=33 autoref clipref html=my_html des='' name="&name"; run; symbol1 value=none interpol=join color=&c1; symbol2 value=none interpol=join color=&c2; symbol3 value=none interpol=join color=&c3; symbol4 value=none interpol=join color=&c4; symbol5 value=none interpol=join color=&c5; symbol6 value=circle height=1.5 c=red interpol=join ci=&c1; axis1 style=0 label=none major=none minor=none offset=(0,0); axis2 style=0 label=none major=none minor=none order=(2000 to 2015 by 5) offset=(0,0); title1 ls=1.5 "Percentage of State Revenue from Federal Funds"; title2 ls=0.8 "Each Line Represents a State, Colored by FY 2015 Quintile"; ods html anchor='line'; data anno_mouseover; set plot_data; xsys='2'; ysys='2'; hsys='3'; when='b'; x=fiscal_year; y=percent_revenue; function='pie'; rotate=360; style='pempty'; size=0.7; color='white'; html=my_html; run; goptions xpixels=800 ypixels=1000; proc gplot data=plot_data anno=anno_mouseover; format percent_revenue percentn7.0; plot percent_revenue*fiscal_year=quintile_var_2015 / skipmiss nolegend vzero vaxis=axis1 haxis=axis2 noframe cvref=gray55 lvref=33 autovref chref=gray55 lhref=33 autohref des='' name="&name"; run; quit; ODS HTML CLOSE; ODS LISTING;