%let name=alternative_unemployment_rates;

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

/*
Article:
http://mercatus.org/publication/comparison-bls-alternative-national-unemployment-rates
Graph:
http://mercatus.org/sites/default/files/unemployment-chart.png

Data:
http://www.bls.gov/webapps/legacy/cpsatab15.htm
click U3, U4, U5, U6 (not seasonally adjusted)
Retrieve data.
Make sure the 'from' date is at least back to 2007
Save the 4 xls spreadsheets into democd71.
(If you add a year, adjust the ranges in the 'proc import's)
*/
%let u3_file=../democd71/SeriesReport-20190129125513_9c8d7c.xlsx;
%let u4_file=../democd71/SeriesReport-20190129125714_4eb17b.xlsx;
%let u5_file=../democd71/SeriesReport-20190129125756_1cb02d.xlsx;
%let u6_file=../democd71/SeriesReport-20190129125836_0b048b.xlsx;

PROC IMPORT DATAFILE="&u3_file" OUT=u3 DBMS=XLSX REPLACE;
RANGE="BLS Data Series$A12:M24";  /* different range than the others */
GETNAMES=YES;
RUN;
proc transpose data=u3 out=u3 (rename=(col1=u3_unemployment _name_=month) drop=_label_);
by year;
run;

PROC IMPORT DATAFILE="&u4_file" OUT=u4 DBMS=XLSX REPLACE;
RANGE="BLS Data Series$A13:M25";
GETNAMES=YES;
RUN;
proc transpose data=u4 out=u4 (rename=(col1=u4_unemployment _name_=month) drop=_label_);
by year;
run;

PROC IMPORT DATAFILE="&u5_file" OUT=u5 DBMS=XLSX REPLACE;
RANGE="BLS Data Series$A13:M25";
GETNAMES=YES;
RUN;
proc transpose data=u5 out=u5 (rename=(col1=u5_unemployment _name_=month) drop=_label_);
by year;
run;

PROC IMPORT DATAFILE="&u6_file" OUT=u6 DBMS=XLSX REPLACE;
RANGE="BLS Data Series$A13:M25";
GETNAMES=YES;
RUN;
proc transpose data=u6 out=u6 (rename=(col1=u6_unemployment _name_=month) drop=_label_);
by year;
run;

/* merge the 4 datasets */
data my_data; merge u3 u4 u5 u6;
run;

data my_data; set my_data (where=(u3_unemployment^=.));
format date date9.;
date=input('15'||trim(left(month))||trim(left(year)),date9.);
format u3_unemployment u4_unemployment u5_unemployment u6_unemployment percentn7.0;
u3_unemployment=u3_unemployment/100;
u4_unemployment=u4_unemployment/100;
u5_unemployment=u5_unemployment/100;
u6_unemployment=u6_unemployment/100;
run;

data my_data; set my_data;
band1_min=.; band1_max=.; band2_min=.; band2_max=.; band3_min=.; band3_max=.; band4_min=.; band4_max=.;
band1_min=0; band1_max=u3_unemployment; 
band2_min=band1_max; band2_max=u4_unemployment; 
band3_min=band2_max; band3_max=u5_unemployment; 
band4_min=band3_max; band4_max=u6_unemployment; 
run;

proc sql noprint;
select max(date) format=monname3. into :max_mon separated by ' ' from my_data;
select max(date) format=year4. into :max_year separated by ' ' from my_data;
quit; run;


/* define light & dark shades of color */
%let lred=cxf2dcdb;
%let dred=cxd76563;
%let lorange=cxf6a05b;
%let dorange=cxf47c1b;
%let lgreen=cx91cf50;
%let dgreen=cx4e9205;
%let lblue=cx9ac3ed;
%let dblue=cx0974e1;

/* y-positions for text boxes & labels */
%let y1anchor=23;
%let y2anchor=38;
%let y3anchor=53;
%let y4anchor=68;

/* annotate the summary info boxes to the right of the graph */

proc sql noprint;
create table my_anno as 
select unique *
from my_data
having date=max(date);
quit; run;


data my_anno; set my_anno;
length function $20 x1space x2space y1space y2space $50 label $100;

layer='front';

/* draw the lines from the last data value to the text box */
function='line'; linethickness=1;
x1space='datavalue'; y1space='datavalue';
x2space='datapercent'; y2space='datapercent';

linecolor="&dblue";
x1=date; y1=u3_unemployment; 
x2=100; y2=&y1anchor;
output;

linecolor="&dgreen";
x1=date; y1=u4_unemployment; 
x2=100; y2=&y2anchor;
output;

linecolor="&dorange";
x1=date; y1=u5_unemployment; 
x2=100; y2=&y3anchor;
output;

linecolor="&dred";
x1=date; y1=u6_unemployment; 
x2=100; y2=&y4anchor;
output;

/* draw the boxes for the text */
function='rectangle'; anchor='topleft'; display='fill';
widthunit='percent'; heightunit='percent'; linethickness=1;
x1space='datapercent'; y1space='datapercent';
x1=.; y1=.; x2=.; y2=.;

linecolor="&dblue"; fillcolor="&lblue";
x1=100; y1=&y1anchor;
width=18; height=12;
output;

linecolor="&dgreen"; fillcolor="&lgreen";
x1=100; y1=&y2anchor;
width=18; height=12;
output;

linecolor="&dorange"; fillcolor="&lorange";
x1=100; y1=&y3anchor;
width=18; height=12;
output;

linecolor="&dred"; fillcolor="&lred";
x1=100; y1=&y4anchor;
width=18; height=12;
output;

/* write the text in the text boxes */
function='text'; anchor='left'; justify='left'; 
textsize=8; textweight='normal'; textcolor='gray33'; fillcolor=''; 
x1space='datapercent'; y1space='datapercent';
x2space=''; y2space='';
x=100;

y1=&y1anchor-3; label='Official U.S. National'; output;
y1=y1-3.1; label='Unemployment Rate'; output;
y1=y1-3.1; label='(U3)'; output;

y1=&y2anchor-3; label='Including Discouraged'; output;
y1=y1-3.1; label='Workers (U4)'; output;

y1=&y3anchor-3; label='Including Marginally-'; output;
y1=y1-3.1; label='Attached Workers (U5)'; output;

y1=&y4anchor-3; label='Including Part-Time'; output;
y1=y1-3.1; label='Workers for Economic'; output;
y1=y1-3.1; label='Reasons (U6)'; output;

run;


ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" 
 (title="Alternative US Unemployment Rates") 
 style=htmlblue;

ods graphics /
 imagefmt=png imagename="&name"
 width=900px height=600px noborder; 

title1 c=gray33 h=13pt "Comparison of US Alternative Unemployment Rates";
title2 h=1pct ' ';

footnote 
 link=''
 c=gray "Data source: U.S. Bureau of Labor Statistics, Table A-15 (as of &max_mon, &max_year)";

proc sgplot data=my_data noautolegend noborder pad=(right=21pct) sganno=my_anno;
format band4_min band3_min band2_min band1_min percentn7.0;
format band4_max band3_max band2_max band1_max percentn7.0;
format date year4.;

/* plot the areas */
band x=date lower=band4_min upper=band4_max / fillattrs=(color=&lred); 
band x=date lower=band3_min upper=band3_max / fillattrs=(color=&lorange); 
band x=date lower=band2_min upper=band2_max / fillattrs=(color=&lgreen); 
band x=date lower=band1_min upper=band1_max / fillattrs=(color=&lblue); 

/* draw the darker lines between the areas */
series x=date y=band4_max / lineattrs=(color=&dred); 
series x=date y=band3_max / lineattrs=(color=&dorange); 
series x=date y=band2_max / lineattrs=(color=&dgreen); 
series x=date y=band1_max / lineattrs=(color=&dblue); 

xaxis display=(nolabel /*noticks*/)
 values=('01jan2007'd to '01jan2020'd by year)
 valueattrs=(size=9 color=gray33)
/* grid is behind graph - so we'll use reflines instead */
/* grid gridattrs=(pattern=dot color=gray66) */
 offsetmin=0 offsetmax=0;

yaxis display=(noline nolabel noticks)
 values=(0 to .18 by .02)
 valueattrs=(size=9 color=gray33)
/* grid gridattrs=(pattern=dot color=gray66) */
 offsetmin=0 offsetmax=0;

/* Use reflines instead of 'grid' option, so they will be in front */
refline '01jan2007'd to '01jan2020'd by /*year*/ 365 / axis=x lineattrs=(pattern=dot color=gray66);
refline .02 to .18 by .02 / axis=y lineattrs=(pattern=dot color=gray66);

run;

quit;
ODS HTML CLOSE;
ODS LISTING;