%let name=job_openings_bls;

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

/*
Similar to graphs in this post by Leonard Kiefer:
https://twitter.com/lenkiefer/status/1115975047078907905

Using data from:
https://data.bls.gov/cgi-bin/surveymost?jt
Selected the same "Job openings rate" as the graphs above.
 selected the JTS (seasonally adjusted) where available.
 Total nonfarm
 Trade, transportation, and utilities
 Professional and business services
 Education and health services
 Leisure and hospitality
 Manufacturing
 State and local  (Government?)
 Construction
 Other services
 Financial activities
 Information
 Mining and logging
Retrieve data.
Changed the 'from' year to 2000.
Clicked 'Go'.
Saved all 12 as .xls
(there might be an easier way to do this...)

---

Definition of job openings rate:
https://www.bls.gov/opub/hom/pdf/jlt-20130314.pdf
"The job openings rate is computed by dividing the 
number of job openings by the sum of the number of 
people employed and the number of job openings and
multiplying the resulting quotient by 100."
*/

%macro read_data(xlsname,dataname,survey);
proc import datafile="&xlsname" dbms=xlsx out=&dataname;
range='BLS Data Series$a12:m32';
getnames=yes;
run;
data &dataname; set &dataname;
length survey $100;
survey="&survey";
run;
%mend;

data myattrs;
length value $100;
id="some_id";
value="Total nonfarm"; fillcolor="cxf377e4"; linecolor=fillcolor; output;
value="Trade, transportation, and utilities"; fillcolor="cxff66b1"; linecolor=fillcolor; output;
value="Professional and business services"; fillcolor="cx629eff"; linecolor=fillcolor; output;
value="Education and health services"; fillcolor="cxde952a"; linecolor=fillcolor; output;
value="Leisure and hospitality"; fillcolor="cx11ae52"; linecolor=fillcolor; output;
value="Manufacturing"; fillcolor="cx2dbc9e"; linecolor=fillcolor; output;
value="Government"; fillcolor="cxc77dff"; linecolor=fillcolor; output;
value="Construction"; fillcolor="cxf77c73"; linecolor=fillcolor; output;
value="Other services"; fillcolor="cx20adee"; linecolor=fillcolor; output;
value="Financial activities"; fillcolor="cxbba21a"; linecolor=fillcolor; output;
value="Information"; fillcolor="cx83a414"; linecolor=fillcolor; output;
value="Mining and logging"; fillcolor="cx02bfc4"; linecolor=fillcolor; output;
run;

%read_data(SeriesReport-20190411072035_0b99d7.xlsx, nonfarm, Total nonfarm);
%read_data(SeriesReport-20190411084943_987910.xlsx, ttu, %bquote(Trade, transportation, and utilities));
%read_data(SeriesReport-20190411091327_530482.xlsx, profbus, Professional and business services);
%read_data(SeriesReport-20190411091852_3ae78c.xlsx, eduhea, Education and health services);
%read_data(SeriesReport-20190411092515_a7430e.xlsx, leishosp, Leisure and hospitality);
%read_data(SeriesReport-20190411092906_c269ba.xlsx, manuf, Manufacturing);
%read_data(SeriesReport-20190411093420_c2726b.xlsx, gov, Government);
%read_data(SeriesReport-20190411094247_8b3456.xlsx, const, Construction);
%read_data(SeriesReport-20190411094638_8479b9.xlsx, serv, Other services);
%read_data(SeriesReport-20190411095010_9fd34a.xlsx, fin, Financial activities);
%read_data(SeriesReport-20190411095445_7d57c5.xlsx, info, Information);
%read_data(SeriesReport-20190411095643_4052a1.xlsx, minlog, Mining and logging);

data my_data; set nonfarm ttu profbus eduhea leishosp manuf gov const serv fin info minlog;
run;

data my_data; set my_data;
by survey notsorted;
if first.survey then original_order+1;
run;

proc sort data=my_data out=my_data;
by survey original_order year;
run;

proc transpose data=my_data out=my_data;
by survey original_order year;
run;

data my_data; set my_data (where=(job_openings_rate^=.) 
 rename=(_name_=month col1=job_openings_rate) drop=_label_);
/* for plotting purposes, assign a date value, in the middle of the month */
format date date9.;
date=input('15'||trim(left(month))||trim(left(year)),date9.);
/* divide by 100, so we can show the rate values as % */
job_openings_rate=job_openings_rate/100;
run;

proc sort data=my_data out=my_data;
by original_order survey year;
run;

data my_data; set my_data;
year=.; year=year(date);
by survey notsorted;
if last.survey then latest=job_openings_rate;
run;

/*
Since ods graphics footnote does not support url links yet,
annotate the footnote (annotated text supports url links).
*/
data anno_footnote;
length label $200 anchor x1space y1space function $50 textcolor $12;
function='text';
x1space='graphpercent'; y1space='graphpercent';
anchor='center';
textcolor="gray77"; textsize=8; textweight='normal';
width=100; widthunit='percent';
x1=50; y1=3;
url="https://data.bls.gov/cgi-bin/surveymost?jt";
label="Data source: U.S. Bureau of Labor Statistics Job Openings and Labor Turnover Survey - Downloaded 11apr2019, values through Feb 2019"; 
output;
/* let's also annotate the title2, so we can get it in normal (non-bold) text */
x1=50; y1=92.5;
url="";
textsize=10;
label="Dashed line  - - -  represents latest value (Feb 2019)"; 
output;
run;


ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" 
 (title="U.S. Job Openings Rate (BLS)") 
 style=htmlblue;

ods graphics /
 imagemap tipmax=25000 
 imagefmt=png imagename="&name"
 width=1000px height=800px noborder;

title1 c=gray33 h=12pt "U.S. Job Openings Rate, by Industry";
title2 h=11pt ls=0.5 ' ';
title3 h=5pt ' ';

footnote h=8pt ' ';
/*
footnote1 c=gray77 h=8pt 
 "Data source: U.S. Bureau of Labor Statistics Job Openings and Labor Turnover Survey (JOLTs)";
*/
/*
pseudo-code
proc sgpanel data=my_data dattrmap=myattrs;
panelby survey / columns=4;
band x=date lower=0 upper=job_openings_rate / group=survey attrid=some_id transparency=.50;
series x=date y=job_openings_rate / group=survey attrid=some_id;
refline latest / axis=y lineattrs=(color=gray77 pattern=shortdash);
scatter x=date y=latest / markerattrs=(color=gray77 symbol=CircleFilled size=8px);
run;
*/

/* blog code */
/*
proc sgpanel data=my_data noautolegend dattrmap=myattrs sganno=anno_footnote;
styleattrs backcolor=white wallcolor=cxfafbfe;
panelby survey / columns=4 sort=data novarname spacing=6 noborder;
format date best12.;
band x=date lower=0 upper=job_openings_rate / fill group=survey attrid=some_id transparency=.50 tip=none;
series x=date y=job_openings_rate / group=survey attrid=some_id
 tip=(year date job_openings_rate) tipformat=(auto monname3. percentn7.1) tiplabel=('Year' 'Month' 'Rate');
refline latest / axis=y lineattrs=(color=gray77 pattern=shortdash);
scatter x=date y=latest / markerattrs=(color=gray77 symbol=CircleFilled size=8px)
 tip=(year date job_openings_rate) tipformat=(auto monname3. percentn7.1) tiplabel=('Year' 'Month' 'Latest Rate');
rowaxis values=(0 to .08 by .02) display=(nolabel noline noticks)
 valueattrs=(color=gray33) valuesformat=percent7.0
 grid offsetmin=0 offsetmax=0;
colaxis
 values=('15jan2000'd to '15jan2020'd by year5)
 valueattrs=(color=gray33)
 valuesdisplay=(' ' '2005' '2010' '2015' '2020')
 display=(nolabel noline noticks) grid offsetmin=0 offsetmax=0;
run;
*/



proc sgpanel data=my_data noautolegend dattrmap=myattrs sganno=anno_footnote;
styleattrs backcolor=white wallcolor=cxfafbfe;
panelby survey / columns=4 sort=data novarname spacing=6 noborder;
/* 
I'm using a numeric format, rather than date format, so that 
I can replace the values with the valuesdisplay= option.
format date year4.;
*/
format date best12.;
band x=date lower=0 upper=job_openings_rate / fill group=survey 
 attrid=some_id transparency=.50 tip=none;
series x=date y=job_openings_rate / group=survey attrid=some_id
 tip=(year date job_openings_rate) tipformat=(auto monname3. percentn7.1) tiplabel=('Year' 'Month' 'Rate');
refline latest / axis=y lineattrs=(color=gray77 pattern=shortdash);
scatter x=date y=latest / markerattrs=(color=gray77 symbol=CircleFilled size=8px)
 tip=(year date job_openings_rate) tipformat=(auto monname3. percentn7.1) tiplabel=('Year' 'Month' 'Latest Rate');
rowaxis values=(0 to .08 by .02) display=(nolabel noline noticks) 
 valueattrs=(color=gray33) valuesformat=percent7.0
 grid offsetmin=0 offsetmax=0;
colaxis 
 values=('15jan2000'd to '15jan2020'd by year5) 
 valueattrs=(color=gray33)
 valuesdisplay=(' ' '2005' '2010' '2015' '2020')
 display=(nolabel noline noticks) grid offsetmin=0 offsetmax=0;
run;

/*
ods graphics / width=800px height=600px noborder;
proc sgplot data=my_data;
format date year4.;
series x=date y=job_openings_rate / group=survey;
yaxis values=(0 to 8 by 2) display=(nolabel) grid offsetmin=0 offsetmax=0;
xaxis values=('15jan2000'd to '15jan2020'd by year5) display=(nolabel) grid offsetmin=0 offsetmax=0;
run;
*/
/*
proc print data=my_data; run;
*/

quit;
ODS HTML CLOSE;
ODS LISTING;