%let name=labor_participation;

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

/*
http://www.smallbizlabs.com/2012/09/workforce-participation-where-is-everyone-going.html
http://www.washingtonpost.com/blogs/wonkblog/post/the-incredible-shrinking-labor-force/2012/05/04/gIQANXAy1T_blog.html

Good description of how things are defined/counted/etc...
http://www.bls.gov/lau/rdscnp16.htm
*/

/*
Got the data from here:
http://data.bls.gov/timeseries/LNS11300000
Change the 'From' year to 1960.
Click 'Go'
Download the xls spreadsheet.
SeriesReport-20140507131410.xls
SeriesReport-20141003144502.xlsx
SeriesReport-20170517093700_0ab034.xlsx
SeriesReport-20181207141228_b08606.xlsx

Series Id:	LNS11300000				
Seasonally Adjusted					
Series title:	(Seas) Labor Force Participation Rate				
Labor force status:	Civilian labor force participation rate				
Type of data:	Percent or rate				
Age:	16 years and over				
Years:	1960 to 2014				
*/

PROC IMPORT OUT=my_data 
 DATAFILE="../democd71/SeriesReport-20181207141228_b08606.xlsx" 
 DBMS=XLSX REPLACE;
RANGE='BLS Data Series$A12:M71';
GETNAMES=YES;
RUN;

proc transpose data=my_data (where=(year^=.)) out=my_data;
by year;
run;

data my_data; set my_data;
format date date9.;
date=input('15'||trim(left(_name_))||trim(left(year)),date9.);
format col1 percentn7.0;
col1=col1/100;  /* convert to percent value (eg, 60 becomes .60, which prints as 60%) */
run;

proc sql noprint;
create table latest as select * from my_data where col1^=.;
create table latest as select *, col1 as latest from latest having date=max(date);
quit; run;
data latest; set latest;
latest_html=
 'title='||quote(lowcase(put(date,monyy7.))||':  '||put(latest,percentn7.1))||
 ' href="labor_participation_info.htm"';
run;


/*
Using dates from:
http://en.wikipedia.org/wiki/List_of_recessions_in_the_United_States
*/
data anno_recessions; 
format recession_start recession_end date9.;
informat recession_start recession_end date9.;
input recession_start recession_end;
datalines;
15apr1960 15feb1961
15dec1969 15nov1970
15nov1973 15mar1975
15jan1980 15jul1980
15jul1981 15nov1982
15jul1990 15mar1991
15mar2001 15nov2001
15dec2007 15jun2009
;
run;

data anno_recessions; set anno_recessions;
length function $20 layer $8 x1space y1space $20 color fillcolor $12 label $100;
/* 
With layer='back' (to get the annotated stuff to be behind the 
sgplot lines), the polygon is drawn behind the white area within 
the graph axes (called the 'wall'). Therefore you must use the sgplot 
'nowall' option, or you won't be able to see the annotate polygons 
because they will be behind the white background wall, and obscured by it.
*/
layer='back';
/* could be outline, fill, or all (default is outline)
I just want the filled polygon, with no outline */
display='fill'; fillcolor='cxf0f1e6'; 
/* now, specify the 4 corners of the polygon */
x1space='datavalue'; y1space='wallpercent';
function='polygon';  x1=recession_start; y1=0; output;
function='polycont'; x1=recession_end;   y1=0; output;
function='polycont'; x1=recession_end;   y1=100; output;
function='polycont'; x1=recession_start; y1=100; output;
run;

data anno_text;
length function $20 layer $8 x1space y1space $20 color fillcolor $12 label $100;


layer="front";
function="text"; textcolor="gray55"; textsize=10;
/*textweight='bold';*/ anchor='left';
width=100; widthunit='percent'; 
x1space='wallpercent';
y1space='wallpercent'; 

comment=1;
x1=6; y1=85; label="The rate began to rise significantly in"; output;
y1=y1-3.6; label="the 1970s and 80s as women's labor"; output;
y1=y1-3.6; label="force participation rates surged,"; output;
y1=y1-3.6; label="and the baby-boom generaton"; output;
y1=y1-3.6; label="entered the labor market."; output;

comment=2;
x1=48; y1=74; label='The rate declined as the most'; output;
y1=y1-3.6; label='recent recession hit the workforce,'; output;
y1=y1-3.6; label='and large numbers of baby boomers'; output;
y1=y1-3.6; label='also started to retire.'; output;

/* just picked an arbitrary date that 'centered' well, for this text label */
/*
comment=3;
textcolor="gray99";
x1space='datavalue'; 
x1='15sep2008'd; y1=1; rotate=90; 
label='Recessions'; output;
*/
run;

data anno_all; set anno_recessions anno_text;
run;



ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" 
 (title="U.S. Labor Force Participation Rate") 
 style=htmlblue;

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

title1 c=gray33 h=17pt "U.S. Civilian Labor Force Participation Rate";
title2 c=gray33 h=12pt ls=0.4 "16 years and over, seasonally adjusted";

footnote 
 link='http://data.bls.gov/timeseries/LNS11300000'
 c=gray "Data source: http://data.bls.gov/timeseries/LNS11300000";

ods html anchor='version1';
proc sgplot data=my_data nowall sganno=anno_all;
format date year4.;
series x=date y=col1 / lineattrs=(color=cxcb633b thickness=2px);
yaxis display=(nolabel) 
 values=(.50 to .70 by .05) grid
 valueattrs=(size=11pt weight=bold color=gray33)
 offsetmin=0 offsetmax=0;
xaxis display=(nolabel) 
 values=('01jan1960'd to '01jan2020'd by year5) 
 valueattrs=(size=11pt weight=bold color=gray33)
 valuesrotate=vertical fitpolicy=rotate notimesplit
 offsetmin=0 offsetmax=0 /* minor minorcount=4 */;
/* hmm ... minor ticks are not allowed when axis has uneven major ticks */
run;

ods html anchor='version2';
proc sgplot data=my_data nowall sganno=anno_all;
format date year4.;
series x=date y=col1 / lineattrs=(color=cxcb633b thickness=2px);
yaxis display=(nolabel)
 values=(.58 to .68 by .01) grid
 valueattrs=(size=11pt weight=bold color=gray33)
 offsetmin=0 offsetmax=0;
xaxis display=(nolabel)
 values=('01jan1960'd to '01jan2020'd by year5)
 valueattrs=(size=11pt weight=bold color=gray33)
 valuesrotate=vertical fitpolicy=rotate notimesplit
 offsetmin=0 offsetmax=0 /* minor minorcount=4 */;
run;

data anno_all; set anno_all;
if comment=2 then y1=y1-15;
run;

ods html anchor='version3';
proc sgplot data=my_data nowall sganno=anno_all;
format date year4.;
series x=date y=col1 / lineattrs=(color=cxcb633b thickness=2px);
yaxis display=(nolabel)
 values=(0 to 1.00 by .20) grid
 valueattrs=(size=11pt weight=bold color=gray33)
 offsetmin=0 offsetmax=0;
xaxis display=(nolabel)
 values=('01jan1960'd to '01jan2020'd by year5)
 valueattrs=(size=11pt weight=bold color=gray33)
 valuesrotate=vertical fitpolicy=rotate notimesplit
 offsetmin=0 offsetmax=0 /* minor minorcount=4 */;
run;

quit;
ODS HTML CLOSE;
ODS LISTING;