%let name=nc_population_age_gender;

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

/*
Went here:
https://www.census.gov/data/tables/2017/demo/popest/state-detail.html
Under 'Datasets' selected "Single Year of Age and Sex Population Estimates: April 1, 2010 to July 1, 2017 - CIVILIAN"
https://www2.census.gov/programs-surveys/popest/datasets/2010-2017/state/asrh/sc-est2017-agesex-civ.csv
Clicked on it/downloaded, and viewed in IE.
Then saved as .xls
sc-est2017-agesex-civ.xls
*/

%let state=North Carolina;

libname datalib '.';

/* Run this part once, and save the results in a permanent sas libname */
/*
proc import out=pop_data datafile="sc-est2017-agesex-civ.xls" dbms=xls replace;
getnames=yes;
run;

data datalib.pop_data (drop = sumlevel region division);
 set pop_data (where=(sumlev=40 and sex in (1,2) and age^=999) rename=(name=statename));
run;
*/

%macro do_plot(year);

data my_data; set datalib.pop_data (where=(statename="&state") keep=statename sex age popest&year._civ);
run;

proc sort data=my_data out=my_data;
by statename sex age;
run;

proc transpose data=my_data out=my_data (drop = _name_ _label_);
by statename sex;
id age;
var popest&year._civ;
run;

proc sql noprint; 
create table left as select sex,  
sum( _0 , _1 , _2 , _3 , _4 ) as g00, 
sum( _5 , _6 , _7 , _8 , _9 ) as g01, 
sum( _10 , _11 , _12 , _13 , _14 ) as g02, 
sum( _15 , _16 , _17 , _18 , _19 ) as g03, 
sum( _20 , _21 , _22 , _23 , _24 ) as g04, 
sum( _25 , _26 , _27 , _28 , _29 ) as g05, 
sum( _30 , _31 , _32 , _33 , _34 ) as g06, 
sum( _35 , _36 , _37 , _38 , _39 ) as g07, 
sum( _40 , _41 , _42 , _43 , _44 ) as g08, 
sum( _45 , _46 , _47 , _48 , _49 ) as g09, 
sum( _50 , _51 , _52 , _53 , _54 ) as g10, 
sum( _55 , _56 , _57 , _58 , _59 ) as g11, 
sum( _60 , _61 , _62 , _63 , _64 ) as g12, 
sum( _65 , _66 , _67 , _68 , _69 ) as g13, 
sum( _70 , _71 , _72 , _73 , _74 ) as g14,
sum( _75 , _76 , _77 , _78 , _79 ) as g15, 
sum( _80 , _81 , _82 , _83 , _84 ) as g16, 
_85 as g17 
from my_data 
where sex=1; 
quit; run; 
proc transpose data=left out=left (rename=(_name_=age_group col1=population)); 
by sex;
run; 
data left; set left; 
label age_group='Age';
population=-1*population; 
run; 

proc sql noprint; 
create table right as select sex,
sum( _0 , _1 , _2 , _3 , _4 ) as g00, 
sum( _5 , _6 , _7 , _8 , _9 ) as g01, 
sum( _10 , _11 , _12 , _13 , _14 ) as g02, 
sum( _15 , _16 , _17 , _18 , _19 ) as g03, 
sum( _20 , _21 , _22 , _23 , _24 ) as g04, 
sum( _25 , _26 , _27 , _28 , _29 ) as g05, 
sum( _30 , _31 , _32 , _33 , _34 ) as g06, 
sum( _35 , _36 , _37 , _38 , _39 ) as g07, 
sum( _40 , _41 , _42 , _43 , _44 ) as g08, 
sum( _45 , _46 , _47 , _48 , _49 ) as g09, 
sum( _50 , _51 , _52 , _53 , _54 ) as g10, 
sum( _55 , _56 , _57 , _58 , _59 ) as g11, 
sum( _60 , _61 , _62 , _63 , _64 ) as g12, 
sum( _65 , _66 , _67 , _68 , _69 ) as g13, 
sum( _70 , _71 , _72 , _73 , _74 ) as g14, 
sum( _75 , _76 , _77 , _78 , _79 ) as g15, 
sum( _80 , _81 , _82 , _83 , _84 ) as g16, 
_85 as g17 
from my_data
where sex=2; 
quit; run; 
proc transpose data=right out=right (rename=(_name_=age_group col1=population)); 
by sex;
run; 

data both; set left right; 
run; 

proc format; 
value sexfmt 1="Male" 2="Female"; 
run; 
 
proc format; 
picture posval low-high='000,009'; 
run; 

proc format; 
 value $agefmt 
 "g17"="85+" 
 "g16"="80-84" 
 "g15"="75-79" 
 "g14"="70-74" 
 "g13"="65-69" 
 "g12"="60-64" 
 "g11"="55-59" 
 "g10"="50-54" 
 "g09"="45-49" 
 "g08"="40-44" 
 "g07"="35-39" 
 "g06"="30-34" 
 "g05"="25-29" 
 "g04"="20-24" 
 "g03"="15-19" 
 "g02"="10-14" 
 "g01"="5-9" 
 "g00"="0-4"; 
run; 

proc sql noprint;
select sum(abs(population)) format=comma20.0 into :malepop separated by ' ' from left; 
select sum(abs(population)) format=comma20.0 into :fempop separated by ' ' from right; 
quit; run;

data anno_text;
length label $100 anchor x1space y1space $50;
layer="front";
function="text"; textcolor="gray77"; textsize=10; textweight='normal';
width=100; widthunit='percent'; 
x1space='datapercent';
y1space='wallpercent'; 
y1=100;
x1=1; anchor='bottomleft'; label="Male total = &malepop"; output;
x1=100-1; anchor='bottomright'; label="Female total = &fempop"; output;
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 $50;
layer="front";
function='text'; textcolor="gray77"; textsize=10; textweight='normal'; 
width=100; widthunit='percent'; 
x1space='graphpercent'; y1space='wallpercent';
x1=50; y1=100;
anchor='bottom';
url="https://www2.census.gov/programs-surveys/popest/datasets/2010-2017/state/asrh/sc-est2017-agesex-civ.csv";
label="Data source: US Census";
run;

data anno_all; set anno_text anno_footnote;
run;


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

ods html anchor="&year";

title1 c=gray33 h=18pt "&state Population for year &year";

proc sgplot data=both noautolegend sganno=anno_all;
format population posval.; 
format age_group $agefmt.;
format sex sexfmt.; 
hbarparm category=age_group response=population / group=sex groupdisplay=stack
 barwidth=1.0 outlineattrs=(color=gray77);
/* 'invisible' scatter plot, just to get the 2nd y axis */
scatter x=population y=age_group / y2axis markerattrs=(size=0px);
styleattrs datacolors=(cx7abfff pink);
yaxis reverse
 display=(noticks)
 valueattrs=(color=gray33 size=10pt)
 labelposition=top labelattrs=(weight=bold)
 colorbands=odd colorbandsattrs=(color=grayf3);
y2axis reverse
 display=(noticks)
 valueattrs=(color=gray33 size=10pt)
 labelposition=top labelattrs=(weight=bold);
xaxis 
 display=(nolabel)
 valueattrs=(color=gray33 size=10pt)
 values=(-400000 to 400000 by 100000) 
 grid gridattrs=(color=graydd)
 minorgrid minorcount=1 minorgridattrs=(color=graydd)
 offsetmin=0 offsetmax=0;
run;

%mend;


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

%do_plot(2010);
%do_plot(2017);

quit;
ODS HTML CLOSE;
ODS LISTING;