%let name=un_population_projections;

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

/*
Downloaded data from here (total population: median & confidence intervals):
http://esa.un.org/unpd/ppp/Data-Output/UN_PPP2010_output-data.htm
http://esa.un.org/unpd/ppp/Data-Output/UN_PPP2010_Output_PopTot.xls
(note: population values are in thousands)

Creating graphs like these:
http://esa.un.org/unpd/ppp/Figures-Output/Population/PPP_Total-Population.htm

Those pages are gone now, but here they are on the "Wayback Machine" archive.
graph:
https://web.archive.org/web/20130116053745/http://esa.un.org/unpd/ppp/Figures-Output/Population/PPP_Total-Population.htm
data:
https://web.archive.org/web/20130315041619/https://esa.un.org/unpd/ppp/Data-Output/UN_PPP2010_output-data.htm
*/

/* 
Each of the different variables is on a different 'sheet' (tab) in the Excel spreadsheet.
Read in each sheet separately, and transpose them...
*/

/*
dbms=xls can't use the year as the variable label,
therefore using dbms=excel
*/

proc import out=Pop_Upper95 datafile="../democd64/UN_PPP2010_Output_PopTot.xls" dbms=EXCEL replace;
sheet="Pop_Upper95$"; 
getnames=yes; 
run;
proc sort data=Pop_Upper95 out=Pop_Upper95;
by country_or_area locid;
run;
proc transpose data=Pop_Upper95 out=Pop_Upper95 (rename=(col1=Pop_Upper95));
by country_or_area locid;
run;
data Pop_Upper95 (drop = locid _name_ _label_); set Pop_Upper95;
year=.; year=_label_; 
run;

proc import out=Pop_Lower95 datafile="../democd64/UN_PPP2010_Output_PopTot.xls" dbms=excel replace;
sheet="Pop_Lower95$"; getnames=yes; mixed=no; scantext=yes;
run;
proc sort data=Pop_Lower95 out=Pop_Lower95;
by country_or_area locid;
run;
proc transpose data=Pop_Lower95 out=Pop_Lower95 (rename=(col1=Pop_Lower95));
by country_or_area locid;
run;
data Pop_Lower95 (drop = locid _name_ _label_); set Pop_Lower95;
year=.; year=_label_; 
run;

proc import out=Pop_Median datafile="../democd64/UN_PPP2010_Output_PopTot.xls" dbms=excel replace;
sheet="Pop_Median$"; getnames=yes; mixed=no; scantext=yes;
run;
proc sort data=Pop_Median out=Pop_Median;
by country_or_area locid;
run;
proc transpose data=Pop_Median out=Pop_Median (rename=(col1=Pop_Median));
by country_or_area locid;
run;
data Pop_Median (drop = locid _name_ _label_); set Pop_Median;
year=.; year=_label_; 
run;

proc import out=Pop_Upper80 datafile="../democd64/UN_PPP2010_Output_PopTot.xls" dbms=excel replace;
sheet="Pop_Upper80$"; getnames=yes; mixed=no; scantext=yes;
run;
proc sort data=Pop_Upper80 out=Pop_Upper80;
by country_or_area locid;
run;
proc transpose data=Pop_Upper80 out=Pop_Upper80 (rename=(col1=Pop_Upper80));
by country_or_area locid;
run;
data Pop_Upper80 (drop = locid _name_ _label_); set Pop_Upper80;
year=.; year=_label_; 
run;

proc import out=Pop_Lower80 datafile="../democd64/UN_PPP2010_Output_PopTot.xls" dbms=excel replace;
sheet="Pop_Lower80$"; getnames=yes; mixed=no; scantext=yes;
run;
proc sort data=Pop_Lower80 out=Pop_Lower80;
by country_or_area locid;
run;
proc transpose data=Pop_Lower80 out=Pop_Lower80 (rename=(col1=Pop_Lower80));
by country_or_area locid;
run;
data Pop_Lower80 (drop = locid _name_ _label_); set Pop_Lower80;
year=.; year=_label_; 
run;

/* combine the separate data sets into 1 data set (so you can plot them together) */

proc sql noprint;

create table all_data as
select unique Pop_Upper95.Country_or_area, Pop_Upper95.year, Pop_Upper95.Pop_Upper95, Pop_Lower95.Pop_Lower95
from Pop_Upper95 left join Pop_Lower95
on (Pop_Upper95.Country_or_area=Pop_Lower95.Country_or_area) and (Pop_Upper95.year=Pop_Lower95.year);

create table all_data as
select unique all_data.*, Pop_Median.Pop_Median
from all_data left join Pop_Median
on (all_data.Country_or_area=Pop_Median.Country_or_area) and (all_data.year=Pop_Median.year);

create table all_data as
select unique all_data.*, Pop_Upper80.Pop_Upper80
from all_data left join Pop_Upper80
on (all_data.Country_or_area=Pop_Upper80.Country_or_area) and (all_data.year=Pop_Upper80.year);

create table all_data as
select unique all_data.*, Pop_Lower80.Pop_Lower80
from all_data left join Pop_Lower80
on (all_data.Country_or_area=Pop_Lower80.Country_or_area) and (all_data.year=Pop_Lower80.year);

quit; run;

data all_data; set all_data;
format Pop_Upper95 Pop_Lower95 Pop_Median Pop_Upper80 Pop_Lower80 comma10.0;
/* you don't want plot markers to the left of current date */
/*
if year<2010 then do;
 Pop_Upper95=.;
 Pop_Lower95=.;
 Pop_Upper80=.;
 Pop_Lower80=.;
 end;
*/
run;

proc sort data=all_data out=all_data;
by country_or_area year;
run;

/* 
Since ods graphics footnote does not support url links yet, 
annotate the footnote (annotated text supports url links).
*/
data anno_footnote;
length label $100 anchor x1space y1space function $50 textcolor $12;
function='text';
x1=50; y1=3;
x1space='wallpercent'; y1space='graphpercent';
anchor='center';
textcolor="gray33"; textsize=9; textweight='normal'; 
width=50; widthunit='percent'; 
/*
url="http://esa.un.org/unpd/ppp/Data-Output/UN_PPP2010_output-data.htm";
*/
url="https://web.archive.org/web/20130315041619/https://esa.un.org/unpd/ppp/Data-Output/UN_PPP2010_output-data.htm";
label="Data source: 2010 World Population Prospects (esa.un.org)";
run;


ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" 
 (title="Population Projections") 
 style=htmlblue;

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

/* simple/uncustomized version */
/*
proc sgplot data=all_data (where=(Country_or_area='Afghanistan'));
by Country_or_area;
band x=year upper=Pop_Upper95 lower=Pop_Upper80 / fillattrs=(color=grayee) transparency=.50 name='band95' legendlabel='95% Confidence Interval';
band x=year upper=Pop_Lower80 lower=Pop_Lower95 / fillattrs=(color=grayee) transparency=.50;
band x=year upper=Pop_Upper80 lower=Pop_Median / fillattrs=(color=grayc3) transparency=.50 name='band80' legendlabel='80% Confidence Interval';
band x=year upper=Pop_Median lower=Pop_Lower80 / fillattrs=(color=grayc3) transparency=.50;
series x=year y=Pop_Median / lineattrs=(color=red thickness=3px) name='line';;
yaxis grid min=0;
xaxis grid;
keylegend 'line' 'band80' 'band95';
run;
*/

options nobyline;
title1 h=15pt c=blue "#byval(Country_or_area) " c=gray33 "Population Projections";
footnote h=1pt ' ';

ods html anchor="#byval(Country_or_area)";

proc sgplot sganno=anno_footnote noborder data=all_data 
 (where=(Country_or_area in (
 'Afghanistan' 
 'Brazil'
 'China' 
 'Cyprus' 
 'India'
 'Italy'
 'Peru'
 'United Arab Emirates'
 'United States of America' 
 'Venezuela (Bolivarian Republic of)'
 )));
by Country_or_area;
band x=year upper=Pop_Upper95 lower=Pop_Upper80 / tip=none
 fillattrs=(color=gray99) transparency=.50 
 name='band95' legendlabel='95% Confidence Interval'; 
band x=year upper=Pop_Upper80 lower=Pop_Median / tip=none
 fillattrs=(color=pink)  transparency=.50 
 name='band80' legendlabel='80% Confidence Interval'; 
band x=year upper=Pop_Median  lower=Pop_Lower80 / tip=none
 fillattrs=(color=pink) transparency=.50; 
band x=year upper=Pop_Lower80 lower=Pop_Lower95 / tip=none
 fillattrs=(color=gray99) transparency=.50; 

series x=year y=Pop_Median / lineattrs=(color=red thickness=3px) 
 name='line' legendlabel='Probabilistic Median';

yaxis display=(noline noticks) 
 labelpos=top label="thousands" thresholdmax=.8
 offsetmax=0 offsetmin=0 valueattrs=(color=gray33)
 grid gridattrs=(color=gray55 pattern=dot) min=0;

xaxis display=(nolabel noline noticks)
 values=(1950 to 2100 by 10)
 offsetmax=0 offsetmin=0 valueattrs=(color=gray33)
 grid gridattrs=(color=gray55 pattern=dot);

keylegend 'line' 'band80' 'band95' / position=topleft location=inside across=1
 opaque noborder outerpad=(top=15px) linelength=20px fillheight=13px
 valueattrs=(color=gray33);

run;

/*
refline 2010 2050 / axis=x lineattrs=(color=grayaa thickness=1px);
*/

quit;
ODS HTML CLOSE;
ODS LISTING;