%let name=recorded_music_revenue;

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

/*
Using data from:
https://www.riaa.com/u-s-sales-database/
*/

proc import 
 file="recorded_music_revenue.xlsx"
 out=my_data dbms=xlsx replace;
getnames=yes;
range='Sheet1$A2:X0';
run;

proc transpose data=my_data out=tran_data (keep = year _label_ col1);
by year;
run;

data tran_data; set tran_data (rename=(_label_=format col1=revenue));
/* convert to billion dollars */
format revenue dollar12.0;
revenue=revenue*1000000000;
run;

data tran_data; set tran_data;
if format='Vinyl Single' then stack_order=1;
if format='LP/EP' then stack_order=2;
if format='Other Tapes' then stack_order=3;
if format='8-Track' then stack_order=4;
if format='Cassette' then stack_order=5;
if format='Cassette Single' then stack_order=6;
if format='CD' then stack_order=7;
if format='Music Video (Physical)' then stack_order=8;
if format='SACD' then stack_order=9;
if format='DVD Audio' then stack_order=10;
if format='CD Single' then stack_order=11;
if format='Download Single' then stack_order=12;
if format='Download Album' then stack_order=13;
if format='Download Music Video' then stack_order=14;
if format='Ringtones & Ringbacks' then stack_order=15;
if format='Kiosk' then stack_order=16;
if format='Paid Subscription' then stack_order=17;
if format='Sound Exchange Distributions' then stack_order=18;
if format='Synchronization' then stack_order=19;
if format='On-Demand Streaming (Ad-Supported)' then stack_order=20;
if format='Other Ad-Supported Streaming' then stack_order=21;
if format='Other Digital' then stack_order=22;
if format='Limited Tier Paid Subscription' then stack_order=23;
run;

proc sort data=tran_data out=tran_data;
by stack_order format;
run;

ODS LISTING CLOSE;
ODS HTML path=odsout body="&name..htm" 
 (title="Recorded Music Revenue") 
 style=htmlblue;

ods graphics /
 noscale /* if you don't use this option, the text will be resized */
 imagemap tipmax=2500 
 imagefmt=png imagename="&name"
 width=900px height=600px noborder; 

title1 color=gray33 height=18pt "U.S. Recorded Music Revenues by format";

footnote color=gray66 height=12pt "Data source: Recording Industry Association of America (RIAA)";

proc sgplot data=tran_data noborder;
label format='Format:';
vbarparm category=year response=revenue / barwidth=1 
 group=format groupdisplay=stack;
yaxis display=(nolabel noticks noline) 
 valueattrs=(color=gray33 size=11pt)
 grid gridattrs=(pattern=dot color=gray88)
 offsetmin=0 offsetmax=0;
xaxis display=(nolabel noticks) 
 valueattrs=(color=gray33 size=9pt)
 values=(1973 to 2018 by 1) valuesrotate=vertical;
run;

/* group the formats into categories */
data tran_data2; set tran_data;
length category $50;
if format in ('Vinyl Single' 'LP/EP') then category='Vinyl';
if format in ('Other Tapes' '8-Track' 'Cassette' 'Cassette Single') then category='Tape';
if format in ('CD' 'Music Video (Physical)' 'SACD' 'DVD Audio' 'CD Single') then category='Disc';
if format in ('Download Single' 'Download Album' 'Download Music Video' 'Ringtones & Ringbacks' 'Kiosk') then category='Download';
if format in ('Paid Subscription' 'Sound Exchange Distributions' 'Synchronization' 'On-Demand Streaming (Ad-Supported)' 'Other Ad-Supported Streaming' 'Other Digital' 'Limited Tier Paid Subscription') then category='Streaming';
run;

/* assign stacking order for the categories */
data tran_data2; set tran_data2;
if category='Vinyl' then stack_order=1;
if category='Tape' then stack_order=2;
if category='Disc' then stack_order=3;
if category='Download' then stack_order=4;
if category='Streaming' then stack_order=5;
run;

proc sql noprint;
create table tran_data2 as 
select unique year, category, stack_order, 
 sum(revenue) format=dollar12.0 as revenue
from tran_data2
group by year, category
order by stack_order;
quit; run;

proc sgplot data=tran_data2 noborder;
label category='Format:';
styleattrs datacolors=(cxf16f72 cxa6cee3 cxb2df8a cxf5d272 cxb4b9b6);
vbarparm category=year response=revenue / barwidth=1 
 group=category groupdisplay=stack outlineattrs=(color=gray77);
yaxis display=(nolabel noticks noline) 
 valueattrs=(color=gray33 size=11pt)
 grid gridattrs=(pattern=dot color=gray88)
 offsetmin=0 offsetmax=0;
xaxis display=(nolabel noticks) 
 valueattrs=(color=gray33 size=9pt) 
 valuesrotate=vertical
 values=(1973 to 2018 by 1) 
 valuesdisplay=(
 '1973' ''
 '1975' '' '' '' ''
 '1980' '' '' '' ''
 '1985' '' '' '' ''
 '1990' '' '' '' ''
 '1995' '' '' '' ''
 '2000' '' '' '' ''
 '2005' '' '' '' ''
 '2010' '' '' '' ''
 '2015' '' ''
 '2018');
keylegend / position=top autoitemsize noborder
 titlelattrs=(color=gray33 size=11pt)
 valueattrs=(color=gray33 size=11pt);
run;

/* Merge in the consumer price index for each year */
libname here '.';
proc sql noprint;
create table tran_data3 as
select unique tran_data2.*, us_cpi_data.cpi,
 revenue/(cpi/100) format=dollar12.0 as revenue_83
from tran_data2 left join here.us_cpi_data
on tran_data2.year=us_cpi_data.year
order by stack_order;
quit; run;

title2 color=gray33 height=13pt "In 1982-84 Dollars";
footnote color=gray66 height=12pt "Data sources: RIAA for Revenue, BLS for CPI";

proc sgplot data=tran_data3 noborder;
label category='Format:';
styleattrs datacolors=(cxf16f72 cxa6cee3 cxb2df8a cxf5d272 cxb4b9b6);
vbarparm category=year response=revenue_83 / barwidth=1
 group=category groupdisplay=stack outlineattrs=(color=gray77);
yaxis display=(nolabel noticks noline)
 valueattrs=(color=gray33 size=11pt)
 grid gridattrs=(pattern=dot color=gray88)
 offsetmin=0 offsetmax=0 thresholdmax=1;
xaxis display=(nolabel noticks)
 valueattrs=(color=gray33 size=9pt)
 valuesrotate=vertical
 values=(1973 to 2018 by 1)
 valuesdisplay=(
 '1973' ''
 '1975' '' '' '' ''
 '1980' '' '' '' ''
 '1985' '' '' '' ''
 '1990' '' '' '' ''
 '1995' '' '' '' ''
 '2000' '' '' '' ''
 '2005' '' '' '' ''
 '2010' '' '' '' ''
 '2015' '' ''
 '2018');
keylegend / position=top autoitemsize noborder
 titlelattrs=(color=gray33 size=11pt)
 valueattrs=(color=gray33 size=11pt);

run;

proc sql noprint;
select max(year) into :curyear separated by ' ' from tran_data3;
select cpi into :curcpi separated by ' ' from here.us_cpi_data where year=&curyear;  
quit; run;

data tran_data4; set tran_data3;
format revenue_cur dollar12.0;
revenue_cur=(&curcpi/cpi)*(revenue);
run;

title2 color=gray33 height=13pt "In &curyear Dollars";
footnote color=gray66 height=12pt "Data sources: RIAA for Revenue, BLS for CPI";

proc sgplot data=tran_data4 noborder;
label category='Format:';
styleattrs datacolors=(cxf16f72 cxa6cee3 cxb2df8a cxf5d272 cxb4b9b6);
vbarparm category=year response=revenue_cur / barwidth=1
 group=category groupdisplay=stack outlineattrs=(color=gray77);
yaxis display=(nolabel noticks noline)
 valueattrs=(color=gray33 size=11pt)
 grid gridattrs=(pattern=dot color=gray88)
 offsetmin=0 offsetmax=0 thresholdmax=1;
xaxis display=(nolabel noticks)
 valueattrs=(color=gray33 size=9pt)
 valuesrotate=vertical
 values=(1973 to 2018 by 1)
 valuesdisplay=(
 '1973' ''
 '1975' '' '' '' ''
 '1980' '' '' '' ''
 '1985' '' '' '' ''
 '1990' '' '' '' ''
 '1995' '' '' '' ''
 '2000' '' '' '' ''
 '2005' '' '' '' ''
 '2010' '' '' '' ''
 '2015' '' ''
 '2018');
keylegend / position=top autoitemsize noborder
 titlelattrs=(color=gray33 size=11pt)
 valueattrs=(color=gray33 size=11pt);
run;


/* Merge in the population */
proc sql noprint;
create table tran_data5 as
select unique tran_data4.*, us_pop_data.population,
 revenue_cur/population format=dollar12.0 as revenue_cur_pp
from tran_data4 left join here.us_pop_data
on tran_data4.year=us_pop_data.year
order by stack_order;
quit; run;

title2 color=gray33 height=13pt "Dollars spent per person (in &curyear dollars)";
footnote color=gray66 height=12pt "Data sources: RIAA for Revenue, BLS for CPI, Census for population";

proc sgplot data=tran_data5 noborder;
label category='Format:';
styleattrs datacolors=(cxf16f72 cxa6cee3 cxb2df8a cxf5d272 cxb4b9b6);
vbarparm category=year response=revenue_cur_pp / barwidth=1
 group=category groupdisplay=stack outlineattrs=(color=gray77);
yaxis display=(nolabel noticks noline)
 valueattrs=(color=gray33 size=11pt)
 grid gridattrs=(pattern=dot color=gray88)
 offsetmin=0 offsetmax=0 thresholdmax=1;
xaxis display=(nolabel noticks)
 valueattrs=(color=gray33 size=9pt)
 valuesrotate=vertical
 values=(1973 to 2018 by 1)
 valuesdisplay=(
 '1973' ''
 '1975' '' '' '' ''
 '1980' '' '' '' ''
 '1985' '' '' '' ''
 '1990' '' '' '' ''
 '1995' '' '' '' ''
 '2000' '' '' '' ''
 '2005' '' '' '' ''
 '2010' '' '' '' ''
 '2015' '' ''
 '2018');
keylegend / position=top autoitemsize noborder
 titlelattrs=(color=gray33 size=11pt)
 valueattrs=(color=gray33 size=11pt);
run;

data line_data; set tran_data5 (where=(year>=2010 and category in ('Vinyl' 'Disc')));
run;

ods graphics / width=800px height=600px;
proc sgplot data=line_data noborder;
styleattrs datacontrastcolors=(cxed4450 cx6eb232);
series x=year y=revenue_cur_pp / group=category 
 curvelabel curvelabelattrs=(size=11pt weight=bold)
 lineattrs=(thickness=3px) markers markerattrs=(size=8pt);
yaxis display=(nolabel noline noticks)
 valueattrs=(color=gray33 size=11pt)
 grid gridattrs=(pattern=dot color=gray88)
 offsetmin=0 offsetmax=.02;
xaxis display=(nolabel) values=(2010 to 2019 by 1)
 valueattrs=(color=gray33 size=9pt);
run;

quit;
ODS HTML CLOSE;
ODS LISTING;