********************************************************************************

******************************************************************************** *Recreational Fishing Benefits Model_05.02.14; *Authors: Matthew LaPenta and Ryan Stapler, Abt Associates Inc.; *Created: 1/31/2008; *Updated: 5/02/2014; *Purpose: This is a SAS program that calculates recreational fishing benefits under the final rule and other options considered.;

******************************************************************************** ********************************************************************************; *SPECIFY INPUT AND OUTPUT FILES*************************************************; *file locations;

%let d=s; *(C, H, or S drive); %let facloc=&d.:\Projects\Tyche\WA 4-05 (316b)\Benefits Estimation\Final_05.02.14\;

%let mainloc=&facloc.Existing_facilities\Rec\; %let inloc=&mainloc.Inputs\; %let outloc=&mainloc.Outputs\; libname out "&outloc."; *input files;

%let fac_input=Facility_Inputs_and_Weights_01.25.14.xls; %let in_rec=Recreational_Benefits_Inputs_05.08.13.xls; %let input2=IE_outputs_25JAN14_adjusted_for_state_regs.xls; %let insheeti=BNB_IMP; %let insheete=BNB_ENT; %let insheetie=BNB_IE; %let insheetrd=REDUCTIONS_DETAIL; %let insheetrpschd=rpschd; *output files;

%let output=RecBenOutputs_&sysdate..xls; %let tables=RecBenTables_&sysdate..xls; %let printedoutput=&outloc.RecBenTables_&sysdate..rtf; %let printedoutput3=&outloc.RecBenTables_&sysdate..csv;

*SET DISCOUNTING AND COMPLIANCE ASSUMPTIONS****************************************;

%let d0=2013; *(d0 is the base year for discounting); %let t0=2014; *(t0 is promulgation year -i.e., 1st year with costs); %let yrsc=51; *(yrsc is the annualization period); %let ieunits=1; *units of im&e data;

%macro cyr; cydata.TMN1 as cyr_base, cydata.TMN1 as cyr_TMN1, cydata.TMN2 as cyr_TMN2, cydata.TMN3 as cyr_TMN3 %mend;

*perform option-specific steps here; %macro roptionmac0(code);

if &code=1 then do;

if UPCASE(option_name)=UPCASE("option.TMN1" ) then option="TMN1"; else if UPCASE(option_name)=UPCASE("option.TMN2" ) then option="TMN2"; else if UPCASE(option_name)=UPCASE("option.TMN3") then option="TMN3";

end; if &code=2 then do;

*SELECT baseline option; if UPCASE(option_name) =UPCASE("option.TMN1") ; *if option_name ="option.MN14";

end; %mend;

%macro roptionmac1; %optionspecific(baseline,baseline,rec_loss); %optionspecific(TMN1,TMN1,rec_loss);Page 1%optionspecific(TMN2,TMN2,rec_loss);

%optionspecific(TMN3,TMN3,rec_loss); %mend;

%macro roptionmac2; %benbyoption(baseline,baseline,NewWeight,cyr_base,base); %benbyoption(rule,TMN1,NewWeight,cyr_TMN1,tmn1); %benbyoption(rule,TMN2,NewWeight,cyr_TMN2,tmn2); %benbyoption(rule,TMN3,NewWeight,cyr_TMN3,tmn3); %mend; %let optionlist=baseline^TMN1^TMN2^TMN3; %let speciestabs=4; *number of options; %let dollartabs=4; *number of options; %let firsttable=1; %let nby=8; *number of tables under each option=2*options; %let nattabs=4; *number of options; %let Optionnames=baseline^TMN1^TMN2^TMN3;

*DEFINE IMPORT AND EXPORT MACROS***************************************************; *This is a macro that can be used to import data from excel; %macro import(infile,insheet,out);

%put "&insheeti" ;

PROC IMPORT OUT= WORK.&out

DATAFILE= "&infile"
DBMS=EXCEL REPLACE; SHEET="&insheet.$";
GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %mend import; *This is a macro that can be used to export data to excel; %macro export(datafile,outfile,sheet); PROC EXPORT DATA= &datafile

OUTFILE= &outfile DBMS=EXCEL replace; SHEET=&sheet;
RUN; %mend export;

*(1) Import and format IM&E Data; * 1a Import IM&E data; %import(&facloc.&input2,&insheeti.,I); %import(&facloc.&input2,&insheete.,E); %import(&facloc.&input2,&insheetie.,ei); %import(&facloc.&input2,&insheetrd.,reductions_detail); %macro iore(lm);

data &lm.1 (keep=option option_name region species_econ lossmode rec_loss region2); length region2 $20. option $8.;

set &lm.;

if metric ="forgo.harv.n.fish" and comrec_type ="rec.types" and species_econ ~="zz.all.species" and region ~="zz.allregions";

%roptionmac0(1);

rename benefit_value_regional=rec_loss;

if region="calif" then

Page 2region2="California"; else if region="northatlantic" then region2="North

Atlantic"; else if region="midatlantic" then region2="Mid-Atlantic";else if region="southatlantic" then region2="South Atlantic"; else if region="gulfmexico" then region2="Gulf of Mexico"; else if region="greatlakes" then region2="Great Lakes"; else if region="inland" then region2="Inland";

run;

data &lm.2 (keep=option option_name region species_econ lossmode rec_loss region2); length region2 $20. option $8. ;

set &lm.;

if metric ="forgo.harv.n.fish" and comrec_type ="rec.types" and species_econ ~="zz.all.species" and region ~="zz.allregions"; %roptionmac0(2); option="baseline";

rename baseline_value_regional=rec_loss; if region="calif" then region2="California"; else if region="northatlantic" then region2="North Atlantic"; else if region="midatlantic" then region2="Mid-Atlantic";else if region="southatlantic" then region2="South Atlantic"; else if region="gulfmexico" then region2="Gulf of Mexico"; else if region="greatlakes" then region2="Great Lakes"; else if region="inland" then region2="Inland";

run;

data &lm.3;

set &lm.2 &lm.1;

run; %mend;

%iore(i); %iore(e); %iore(ei);

data ie2 (drop=region option_name);

set i3 e3;

if species_econ="z.trophic.xfer" then species_econ="z.trophic.transfer";

run;

data ei_all;

set ei3 i3 e3;

run;

%export(ei3,"&outloc.&output",iecombined); %export(ie2,"&outloc.&output",ieseperate); %export(ei_all,"&outloc.&output",ieallmodes);

Page 3*(2) Import and Format Fish Values Data;

%import(&inloc.&in_rec,Value_Per_FISH,value);

*(3) Import and format species map for mapping the species var in the IM&E data w/ species var in fish value data; * 3a Import species map; %import(&inloc.&in_rec,speciesmap,map);

* 3b format species map data by dropping repeated rows; proc sort data=map out=map2; by Region Specific_Species Proper_Name; run; data map3 (keep= Region Specific_Species Proper_Name) check1;

set map2;

by Region Specific_Species Proper_Name; if first.Region | first.Specific_Species | first.Proper_Name then output map3; else output check1;

run;

*(4) Join Species Map With Fish Value Data, so fish value data can be matched to the I&E data.; * 4a join data by region and species; proc sql;

create table value2 as select

m.Specific_Species,v.* from value as v left join map3 as m on v.Species=m.Proper_Name and v.Region=m.region order by region,species,Specific_Species,group; quit; * 4b check for errors; data _null_;

set value2;

if Specific_Species=" " then put "ERROR 1 " Species;

run; proc sql;

create table temp as select

Specific_Species,region,species,group,lp,mp,hp, mean(lp) as meanlp, mean(mp) as meanmp, mean(Hp) as meanHp FROM VALUE2 group by Specific_Species,region,species,group; quit; data check2;

set temp;

if meanlp~=lp | meanmp~=mp | meanhp~=hp then put "ERROR 2 "; if meanlp~=lp | meanmp~=mp | meanhp~=hp;

run; proc sql;

create table temp as select

region,group,species,lp,mp,hp, mean(lp) as meanlp, mean(mp) as meanmp, mean(Hp) as meanHp FROM VALUE2 group by region,group; quit;

* 4c drop any extra rows of data (each row should have a unique region-species combination); data value3a (drop =I_or_E);

set value2;

by region species Specific_Species group;

if first.region | first.species | first.Specific_Species | first.group; *delete species with no benefits;

if group="generic freshwater" and species="Freshwater drum" and region="Mid-Atlantic" then delete;

run;

Page 4* 4d Adjust for Inflation;

%import(&facloc.&fac_input,CPI,cpi1); data cpi2 (keep=CPI);

set cpi1;

if year=2004;

run;

data value3 (drop=CPI);

if _n_=1 then set cpi2; set value3a;

lp=lp*CPI; mp=mp*CPI; hp=hp*CPI;

run;

* (5) Combine Fish Value Data and I&E Data; proc sql;

create table value4 as select

value3.*,ie2.* from value3 inner join ie2

on value3.region=ie2.region2 and value3.Specific_Species=ie2.species_econ; quit;

proc sql;

create table checkvalue as select

value3.*,ie2.* from value3 right join ie2

on value3.region=ie2.region2 and value3.Specific_Species=ie2.species_econ where option="baseline" and value3.lp=. order by ie2.region2,ie2.species_econ;

quit;

data checkvalue2 (keep=region2 species_econ);

set checkvalue;

by region2 species_econ; if first.species_econ then put "error checkvalue2 " region2 species_econ " is being dropped";

if first.species_econ;

run; * (6) Generate Option Specific Total Benefit Files; * 6a Baseline (Eliminate all IM&E) Option; %macro optionspecific(name,code,lossvariable); data v0;set value4;

*recode group variables for species without groupings; if group="generic saltwater"

then do; group ="unidentified";

species ="unidentified"; end;

if species="Pink shrimp"

then do;

end;

if group="generic freshwater"

then do; group ="unidentified";

species ="unidentified"; end;

run;

Page 5proc sql;

create table value&name.IMP as select

region,option, sum(&lossvariable.)*&ieunits. as matequivsI format= 20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLI format= 20.0, sum(mp*&lossvariable.)*&ieunits. as totalbenefitMI format= 20.0, sum(hp*&lossvariable.)*&ieunits. as totalbenefitHI format= 20.0 from v0 where lossmode='imp' and option="&code." group by region,option order by option,region;

create table value&name.ENT as select

region,option, sum(&lossvariable.)*&ieunits. as matequivsE format= 20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLE format= 20.0, sum(mp*&lossvariable.)*&ieunits. as totalbenefitME format= 20.0, sum(hp*&lossvariable.)*&ieunits. as totalbenefitHE format= 20.0 from v0 where lossmode='ent' and option="&code." group by region,option order by option,region;

create table value&name. as select

i.region,i.option, e.matequivsE format= 20.0, i.matequivsI format= 20.0, e.matequivsE+i.matequivsI as matequivs format= 20.0,

e.totalbenefitLE format= 20.0, i.totalbenefitLI format= 20.0, e.totalbenefitLE+i.totalbenefitLI as totalbenefitL format= 20.0,

e.totalbenefitME format= 20.0, i.totalbenefitMI format= 20.0, e.totalbenefitME+i.totalbenefitMI as totalbenefitM format= 20.0,

e.totalbenefitHE format= 20.0, i.totalbenefitHI format= 20.0, e.totalbenefitHE+i.totalbenefitHI as totalbenefitH format= 20.0

from value&name.ENT as e full join value&name.IMP as i on

i.region=e.region; quit;

proc sql;

create table value&name.IMP_S as select

region,option,group,species, sum(&lossvariable.)*&ieunits. as matequivsI format= 20.0,

MAX(lp) as LP format=dollar5.2, MAX(mp) as MP format=dollar5.2, MAX(hp) as HP format=dollar5.2, MIN(lp) as LPqa format=dollar5.2, MIN(mp) as MPqa format=dollar5.2, MIN(hp) as HPqa format=dollar5.2,

sum(lp*&lossvariable.)*&ieunits. as totalbenefitLI format= 20.0, sum(mp*&lossvariable.)*&ieunits. as totalbenefitMI format= 20.0, sum(hp*&lossvariable.)*&ieunits. as totalbenefitHI format= 20.0 from v0 where lossmode='imp' and option="&code." group by region,option,group,species order by option,region,group,species;

create table value&name.ENT_S as select

region,option,group,species,

MAX(lp) as LP format=dollar5.2, MAX(mp) as MP format=dollar5.2, MAX(hp) as HP format=dollar5.2, MIN(lp) as LPqa format=dollar5.2, MIN(mp) as MPqa format=dollar5.2, MIN(hp) as HPqa format=dollar5.2,

Page 6sum(&lossvariable.)*&ieunits. as matequivsE format= 20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLE format= 20.0, sum(mp*&lossvariable.)*&ieunits. as totalbenefitME format= 20.0, sum(hp*&lossvariable.)*&ieunits. as totalbenefitHE format= 20.0 from v0 where lossmode='ent' and option="&code." group by region,option,group,species order by option,region,group,species;

create table X as select

i.region as iregion,i.option as ioption,i.group as igroup,i.species as ispecies,

e.region as eregion,e.option as eoption,e.group as egroup,e.species as especies,

max(i.lp,e.lp) as LP, max(i.Mp,e.Mp) as MP, max(i.Hp,e.Hp) as HP, max(i.lpqa,e.lpqa) as LPqa, max(i.Mpqa,e.Mpqa) as MPqa, max(i.Hpqa,e.Hpqa) as HPqa,

e.matequivsE format= 20.0, i.matequivsI format= 20.0, e.matequivsE+i.matequivsI as matequivs format= 20.0,

e.totalbenefitLE format= 20.0, i.totalbenefitLI format= 20.0, e.totalbenefitLE+i.totalbenefitLI as totalbenefitL format= 20.0,

e.totalbenefitME format= 20.0, i.totalbenefitMI format= 20.0, e.totalbenefitME+i.totalbenefitMI as totalbenefitM format= 20.0,

e.totalbenefitHE format= 20.0, i.totalbenefitHI format= 20.0, e.totalbenefitHE+i.totalbenefitHI as totalbenefitH format= 20.0 from value&name.ENT_S as e full join value&name.IMP_S as i on

i.region=e.region and i.species=e.species; quit;

data value&name._S (keep=region option group species

LP MP HP matequivs totalbenefitL totalbenefitM totalbenefitH matequivsi totalbenefitLi totalbenefitMi totalbenefitHi matequivse totalbenefitLe totalbenefitMe totalbenefitHe); retain region option group species

LP MP HP matequivs totalbenefitL totalbenefitM totalbenefitH matequivsi totalbenefitLi totalbenefitMi totalbenefitHi matequivse totalbenefitLe totalbenefitMe totalbenefitHe;
set X; if iregion~=" " then region=iregion; else region=eregion; if ioption~=" " then option=ioption;

else option=eoption; if igroup~=" " then group=igroup;

else group=egroup; if ispecies~=" " then species=ispecies;

else species=especies;

matequivs=sum(matequivsE,matequivsI);

totalbenefitL=sum(totalbenefitLI,totalbenefitLE); totalbenefitM=sum(totalbenefitMI,totalbenefitME); Page 7totalbenefitH=sum(totalbenefitHI,totalbenefitHE); format matequivs matequivsI matequivsE 20.0; format totalbenefitL totalbenefitM totalbenefitH totalbenefitLi totalbenefitMi totalbenefitHi totalbenefitLe totalbenefitMe totalbenefitHe 20.0; if LP~=LPqa then do; Put "LP~=LPqa"; put region option group species LP LPqa; end; if MP~=MPqa then do;

Put "MP~=MPqa"; put region option group species MP MPqa; end; if HP~=HPqa then do;

Put "HP~=HPqa"; put region option group species HP HPqa; end;

run;

proc sql;

create table value&name.IMP_G as select

region,option,group,"Total" as species,

MAX(lp) as LP format=dollar5.2, MAX(mp) as MP format=dollar5.2, MAX(hp) as HP format=dollar5.2,

sum(&lossvariable.)*&ieunits. as matequivsI format= 20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLI format= 20.0, sum(mp*&lossvariable.)*&ieunits. as totalbenefitMI format= 20.0, sum(hp*&lossvariable.)*&ieunits. as totalbenefitHI format= 20.0 from v0 where lossmode='imp' and option="&code." group by region,option,group order by option,region,group;

create table value&name.ENT_G as select

region,option,group,"Total" as species,

MAX(lp) as LP format=dollar5.2, MAX(mp) as MP format=dollar5.2, MAX(hp) as HP format=dollar5.2,

sum(&lossvariable.)*&ieunits. as matequivsE format= 20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLE format= 20.0, sum(mp*&lossvariable.)*&ieunits. as totalbenefitME format= 20.0, sum(hp*&lossvariable.)*&ieunits. as totalbenefitHE format= 20.0 from v0 where lossmode='ent' and option="&code." group by region,option,group order by option,region,group;

create table Y as select

i.region as iregion,i.option as ioption,i.group as igroup,i.species as ispecies,

e.region as eregion,e.option as eoption,e.group as egroup,e.species as especies,

e.matequivsE format= 20.0, i.matequivsI format= 20.0, e.matequivsE+i.matequivsI as matequivs format= 20.0,

max(i.lp,e.lp) as LP, max(i.Mp,e.Mp) as MP, max(i.Hp,e.Hp) as HP,

e.totalbenefitLE format= 20.0, i.totalbenefitLI format= 20.0, e.totalbenefitLE+i.totalbenefitLI as totalbenefitL format= 20.0,

e.totalbenefitME format= 20.0, Page 8i.totalbenefitMI format= 20.0, e.totalbenefitME+i.totalbenefitMI as totalbenefitM format= 20.0,

e.totalbenefitHE format= 20.0, i.totalbenefitHI format= 20.0, e.totalbenefitHE+i.totalbenefitHI as totalbenefitH format= 20.0 from value&name.ENT_G as e full join value&name.IMP_G as i on

i.region=e.region and i.group=e.group; quit;

data value&name._G (keep=region option group species

LP MP HP matequivs totalbenefitL totalbenefitM totalbenefitH matequivsi totalbenefitLi totalbenefitMi totalbenefitHi matequivse totalbenefitLe totalbenefitMe totalbenefitHe); retain region option group species

LP MP HP matequivs totalbenefitL totalbenefitM totalbenefitH matequivsi totalbenefitLi totalbenefitMi totalbenefitHi matequivse totalbenefitLe totalbenefitMe totalbenefitHe;
set Y; if iregion~=" " then region=iregion; else region=eregion; if ioption~=" " then option=ioption;

else option=eoption; if igroup~=" " then group=igroup;

else group=egroup; if ispecies~=" " then species=ispecies;

else species=especies;

matequivs=sum(matequivsE,matequivsI);

totalbenefitL=sum(totalbenefitLI,totalbenefitLE); totalbenefitM=sum(totalbenefitMI,totalbenefitME); totalbenefitH=sum(totalbenefitHI,totalbenefitHE); format matequivs matequivsI matequivsE 20.0; format totalbenefitL totalbenefitM totalbenefitH totalbenefitLi totalbenefitMi totalbenefitHi totalbenefitLe totalbenefitMe totalbenefitHe 20.0;

run;

proc sort data=value&name._s out=v1; by region group species;

run; data v2;set v1;by region group species; if first.group then order=1; else order+1;

run; data v3;set value&name._g;

order=100;

run;

data v4 (keep = region group species matequivs matequivsi matequivse LP MP HP totalbenefitL totalbenefitM totalbenefitH totalbenefitLi totalbenefitMi totalbenefitHi Page 9totalbenefitLe totalbenefitMe

totalbenefitHe gorder order); set v2 v3; if group="other saltwater" | group="other freshwater" then gorder=2; else if group="unidentified" then gorder=3; else gorder=1;

run;

proc sql;

create table v5 as select

region,"Total" as group,"Total" as species, sum(matequivsi) as matequivsi, sum(matequivse) as matequivse, sum(matequivs) as matequivs,

sum(totalbenefitL) as totalbenefitL, sum(totalbenefitM) as totalbenefitM, sum(totalbenefitH) as totalbenefitH,

sum(totalbenefitLi) as totalbenefitLi, sum(totalbenefitMi) as totalbenefitMi, sum(totalbenefitHi) as totalbenefitHi,

sum(totalbenefitLe) as totalbenefitLe, sum(totalbenefitMe) as totalbenefitMe, sum(totalbenefitHe) as totalbenefitHe,

4 as gorder,1 as order from v4 where species~="Total" group by region; quit;

data v6;retain region group species matequivsi matequivse matequivs

LP MP HP totalbenefitL totalbenefitM totalbenefitH totalbenefitLi totalbenefitMi totalbenefitHi totalbenefitLe totalbenefitMe totalbenefitHe;

length group species $50; set v5 v4; format LP MP HP 7.2;

run; proc sort data=v6 out=v7; by region gorder group order; run;

data byspecies_&name. ;

retain option printspecies; set v7;length option $50;

length printspecies $100; option="&name"; if group="unidentified" & species~="Total" then delete; x="Total ("; y=propcase(group); z=")";

if species~="Total" then printspecies=" "||species;

else if species="Total" and group~="Total" then printspecies=cats(x,y,z); else printspecies="Total (All Species)";

run;

Page 10data Tbyspecies_&name. (keep=region gorder group order

printspecies fish_i fish_e fish_ie ); set byspecies_&name.;

format fish_i fish_e fish_ie 30.0; fish_i =matequivsi; fish_e =matequivse; fish_ie =matequivs;

run;

%mend optionspecific; %roptionmac1;

* (7) Calculate Benefits Schedule; * 7a Import facility data with both facility id's; %import(&facloc.&fac_input,IDMAP,f1); * 7b Import data with weights; %import(&facloc.&fac_input,ID_AND_WEIGHT,ID_AND_WEIGHT); * 7c Import data with region and flow; %import(&facloc.&fac_input,flowdata,f3); * 7d Import data with compliance dates; %import(&facloc.&fac_input,complianceyear,cydata); * 7e Get ID for joining datasets; proc sql;

create table f4 as select

id.plntcode, w.id as facility, w.ps_wgt as Weighting_Factor from f1 as id full join ID_AND_WEIGHT as w on id.surveyid=w.id; quit;

* 7f Drop double-counted plants; proc sort data=f4 out=f5; by plntcode; run; data f6;set f5;by plntcode; if (not first.plntcode | not last.plntcode) and Weighting_Factor=0 then delete;

run;

* 7f join flow and weight data; proc sql;

create table f7 as select

f6.*, f3.Benefits_Region,f3.AverageFlow from f6 full join f3 on f6.plntcode=f3.EIA_Code where f3.EIA_Code~=.; quit;

* 7g recode region variable; data f8 (keep=region AverageFlow Weighting_Factor PLNTCODE facility);

set f7;format region $14.;

if Benefits_Region="CN" | Benefits_Region="CS" | Benefits_Region="CAL" | Benefits_Region="HI" then region="California";

else if Benefits_Region="GL" then region="Great Lakes"; else if Benefits_Region="INL" then region="Inland"; else if Benefits_Region="MA" then region="Mid-Atlantic"; else if Benefits_Region="Mex" then region="Gulf of Mexico"; else if Benefits_Region="NA" then region="North Atlantic"; else if Benefits_Region="SA" then region="South Atlantic";

run;

* 7h combine and format data with average flow, region, wgt, and compliance Page 11year;

proc sql;

create table s2a as select

f8.plntcode,f8.AverageFlow as avgflow,f8.Region,f8.Weighting_Factor as wgt,

f8.Weighting_Factor as NewWeight, cydata.base_newtech as base_newtech, cydata.tmn1_newtech as tmn1_newtech, cydata.tmn2_newtech as tmn2_newtech, cydata.tmn3_newtech as tmn3_newtech, %cyr from f8 full join cydata on cydata.id=f8.facility; quit;

* 7h1i combine new weights data with data on average flow, region, wgt, and compliance year; proc sql;

create table s2 as select

s2a.* from s2a; quit;

data s2;set s2;

if region ="" then delete;
RUN;

* 7i define flow categories and create option-specific datasets;

data baseline rule;

set s2;output baseline rule;

run;

* 7j This macro calculates benefits over time assuming a 0%, 3%, and 7% discount rate; %macro benbyoption(schedule,opt,weight,cyr,opt_short); * 7j1 generate benefits schedule; data s3;set &schedule;

where region~="Virgin Islands";

if &weight.="." then &weight.=0; array flowy_(60) flowy_1-flowy_60; array bio(60) bio_1-bio_60; array bschd(60) bschd_1-bschd_60;

do i=1 to 60;

if "&opt"="baseline" then do;

flowy_(i)=avgflow*&weight.;

if region="Inland" | region="Great Lakes" | region="Gulf of Mexico" then do;*benefits begin in first year for these regions(i=1 in 2012); if i=1 then bio(i)=.1; if i=2 then bio(i)=.2; if i=3 then bio(i)=.8; if i=4 then bio(i)=.9; if i=5 then bio(i)=.95; if i=6 then bio(i)=1; if i>6 and i<=46 then bio(i)=1; if i=47 then Page 12bio(i)=.9; if i=48 then

bio(i)=.8; if i=49 then bio(i)=.2; if i=50 then bio(i)=.1; if i=51 then bio(i)=.05; if i>51 then bio(i)=0; end; else do;*benefits begin a year later for other regions(i=1 in 2010); if i=1 then bio(i)=0; if i=2 then bio(i)=.1; if i=3 then bio(i)=.2; if i=4 then bio(i)=.8; if i=5 then bio(i)=.9; if i=6 then bio(i)=.95; if i=7 then bio(i)=1; if i>7 and i<=46 then bio(i)=1; if i=47 then bio(i)=.9; if i=48 then bio(i)=.8; if i=49 then bio(i)=.2; if i=50 then bio(i)=.1; if i=51 then bio(i)=.05; if i>51 then bio(i)=0;

end; if _n_=1 then put "BASELINE"; end;

else do; if region="Inland" | region="Great Lakes" | region="Gulf of Mexico" then do; *benefits begin in first year for these regions; x=i-&cyr.+&t0.; *e.g., if i=1 and cyr = 2012 and first year of rule is 2012 then x=1 in 2012 (i=1 in 2012); if x<=0 then flowy_(i)=0; *x=1 in first year of compliance; else flowy_(i)=avgflow*&weight.*&opt_short._newtech; if _n_=1 then put "Not BASELINE";

if x<=0 then bio(i)=0; else if x=1 then bio(i)=.1; else if x=2 then bio(i)=.2; else if x=3 Page 13then bio(i)=.8; else if x=4

then bio(i)=.9; else if x=5 then bio(i)=.95; else if x=6 then bio(i)=1; else if x=7 then bio(i)=1; else if x=8 then bio(i)=1; else if x=9 then bio(i)=1; else if x>=9 and i<=46 then bio(i)=1; else if i=47 then bio(i)=.9; else if i=48 then bio(i)=.8; else if i=49 then bio(i)=.2; else if i=50 then bio(i)=.1; else if i=51 then bio(i)=0.05; else if i>51 then bio(i)=0;

end; else do;*benefits begin in second year for these regions; x=i-&cyr.+&t0.; *e.g., if i=1 and cyr = 2012 and first year of rule is 2012 then x=1 in 2012(i=1 in 2012); if x<0 then flowy_(i)=0;*x=1 in second year of compliance; else flowy_(i)=avgflow*&weight.*&opt_short._newtech; if _n_=1 then put "Not BASELINE";

if x<=0 then bio(i)=0; else if x=1 then bio(i)=0; else if x=2 then bio(i)=.1; else if x=3 then bio(i)=.2; else if x=4 then bio(i)=.8; else if x=5 then bio(i)=.9; else if x=6 then bio(i)=.95; else if x=7 then bio(i)=1; else if x=8 then bio(i)=1; else if x=9 then bio(i)=1; else if x=10 then bio(i)=1; else if x>=10 and i<=46 then bio(i)=1; else if i=47 then bio(i)=.9; else if i=48 Page 14then bio(i)=.8; else if i=49

then bio(i)=.2; else if i=50 then bio(i)=.1; else if i=51 then bio(i)=0.05; else if i>51 then bio(i)=0;

end; end;

bschd(i)=(flowy_(i)*bio(i)); end;

run;

* 7j2 combine data with benefits values and benefits schedule data; proc sql;

create table s4 as select

s3.*, v.totalbenefitL,v.totalbenefitM,v.totalbenefitH, v.totalbenefitLi,v.totalbenefitMi,v.totalbenefitHi, v.totalbenefitLe,v.totalbenefitMe,v.totalbenefitHe

from s3 left join value&opt. as v on s3.region=v.region; quit;

* 7j3 calculate total average flow by region; proc sql;

create table s5 as select

s4.*, sum(avgflow*&weight.*&opt_short._newtech) as totalavgflow from s4 group by region; quit;

*create separate regional flow total depending on compliance year;

Data s5;set s5;

if &cyr.=2019 then avgflow_imp=avgflow; if &cyr.>2019 then avgflow_tow=avgflow;
RUN;

proc sql;

create table s5x as select s5.*, sum (avgflow_imp*&weight.*&opt_short._newtech) as totalavgflow_imp, sum (avgflow_tow*&weight.*&opt_short._newtech) as totalavgflow_tow from s5 group by region; quit;

Data s5x;

set s5x; if totalavgflow_imp="." then totalavgflow_imp= 0; if totalavgflow_tow="." then totalavgflow_tow= 0; drop avgflow_imp avgflow_tow;
RUN;

*MAKE CHANGES TO S5 FOR COMPLIANCE SCHEDULE****************************************; Data s5x;

Set s5x; if region="" then delete; if &cyr. = "." then delete;
RUN; Data reductions_detail_1; Page 15Set reductions_detail; impingement_fraction=(1-ccr_fraction_imp); if option_name ="option.tmn1" then option_name="if_tmn1"; else if option_name="option.tmn2" then option_name="if_tmn2"; else if option_name="option.tmn3" then option_name="if_tmn3"; drop ccr_fraction_imp;
RUN;

Proc sort data=reductions_detail_1 out=reductions_detail_2;

by region;
RUN;

Proc transpose data=reductions_detail_2 out=reductions_detail_3;

by region;
RUN;

Data reductions_detail_4 (rename=(col1=if_tmn1) rename=(col2=if_tmn2) rename=(col3=if_tmn3));

format region $20.; set reductions_detail_3; *include baseline value equal to the impingement only option; if region="calif" then region="California";

else if region="greatlakes" then region="Great Lakes"; else if region="gulfmexico" then region="Gulf of Mexico"; else if region="inland" then region="Inland"; else if region="midatlantic" then region="Mid-Atlantic"; else if region="northatlantic" then region="North Atlantic"; else if region="southatlantic" then region="South Atlantic"; drop _NAME_ _LABEL_;
RUN;

Data reductions_detail_5;

set reductions_detail_4; if_baseline=1;
RUN;

Data S5_1;

merge S5x reductions_detail_5; by region; drop totalbenefitL totalbenefitM totalbenefitH;
RUN;

*CALCULATIONS FOR REPOWERINGS****************************************************** note: these calculations are no longer used in the analysis for the final rule and other options considered;

%import(&facloc.&fac_input,rpschd,rpschd); %import(&facloc.&input2,rep_adj,rep_adj);

Proc sort data=rep_adj out=rep_adj;

by option;
RUN; Proc sort data=rpschd out=rpschd;

by option;
RUN;

Data rep_adj;

set rep_adj; if option="baseline_0" then delete; if option="tmn1_0" then delete; if option="tmn2_0" then delete; if option="tmn3_0" then delete;
RUN;

Data rpschd;

set rpschd; if option="baseline_0" then delete; if option="tmn1_0" then delete; Page 16if option="tmn2_0" then delete; if option="tmn3_0" then delete;
RUN;

Data repow;

merge rep_adj rpschd; by option; adj=1/rp_adj; drop rp_adj;
RUN;

Data repow_1;

length region $20.; format region $20.;
set repow; array rpschd_(60) rpschd_1-rpschd_60; array rpschd_adj_(60) rpschd_adj_1-rpschd_adj_60; do i=1 to 60; rpschd_adj_(i)=rpschd_(i)*(1-adj); if rpschd_(i)=0 then rpschd_adj_(i)=0; end; drop rpschd_1-rpschd_60 i; if lossmode="ie" then delete; if region="zz.allregions" then delete; if region="calif" then region="California"; if region="greatlakes" then region="Great Lakes"; if region="gulfmexico" then region="Gulf of Mexico"; if region="northatlantic" then region="North Atlantic"; if region="midatlantic" then region="Mid-Atlantic"; if region="inland" then region="Inland"; if region="southatlantic" then region="South Atlantic"; if option="tmn1" then option="TMN1"; if option="tmn2" then option="TMN2"; if option="tmn3" then option="TMN3";
RUN;

*separate repowering schedules/values for IM&E losses;

Data repow_1imp;

set repow_1; if lossmode~="imp" then delete; array rpschd_adj_(60) rpschd_adj_1-rpschd_adj_60; array rpschd_imp_(60) rpschd_imp_1-rpschd_imp_60; do i=1 to 60; rpschd_imp_(i)=rpschd_adj_(i); end; adj_imp=adj; drop lossmode rpschd_adj_1-rpschd_adj_60 i adj; if option~="&opt." then delete;
RUN;

Data repow_1ent;

set repow_1; if lossmode~="ent" then delete; array rpschd_adj_(60) rpschd_adj_1-rpschd_adj_60; array rpschd_ent_(60) rpschd_ent_1-rpschd_ent_60; do i=1 to 60; rpschd_ent_(i)=rpschd_adj_(i); end; adj_ent=adj; drop lossmode rpschd_adj_1-rpschd_adj_60 i adj; if option~="&opt." then delete;
RUN;

Proc sort data=repow_1imp out=repow_1imp;

by region;
RUN;

Proc sort data=repow_1ent out=repow_1ent; Page 17by region;

RUN;

Data S5_1;

merge S5_1 repow_1imp repow_1ent; by region;
RUN;

********************************************************************************* ******; * 7j4 calculate benefits schedule and per-facility benefits for 0%, 3%, and 7% discount rates; data REC_&opt.FULL (drop=bio_1-bio_60 flowy_1-flowy_60);

set s5_1; array bschd_i_(60) bschd_i_1-bschd_i_60;

array bschd_t_(60) bschd_t_1-bschd_t_60; array bschd(60) bschd_1-bschd_60; array bschd_b_(60) bschd_b_1-bschd_b_60; array rpschd_imp_(60) rpschd_imp_1-rpschd_imp_60; array rpschd_ent_(60) rpschd_ent_1-rpschd_ent_60;

do i=1 to 60; *schedule for facilities getting only IM tech;

if &cyr.=2019 and totalavgflow_imp>0 then bschd_i_(i)=bschd(i)/totalavgflow_imp; if &cyr.=2019 and totalavgflow_imp = 0 then bschd_i_(i)=0; if &cyr.>2019 then bschd_i_(i)=0;

*schedule for facilities getting a tower (includes those getting both IM tech and tower); if &cyr.>2019 and totalavgflow_tow>0 then bschd_t_(i)=bschd(i)/totalavgflow_tow; if &cyr.>2019 and totalavgflow_tow = 0 then bschd_t_(i)=0; if &cyr.=2019 then bschd_t_(i)=0;

*schedule for baseline (based on total weighted flow); bschd_b_(i)=bschd(i)/totalavgflow;

end;

%macro LMH(k); array benefit&k.0(60) benefit&k.0_1-benefit&k.0_60; array benefit&k.3(60) benefit&k.3_1-benefit&k.3_60; array benefit&k.7(60) benefit&k.7_1-benefit&k.7_60;

do i=1 to 60; if "&opt"="baseline" then benefit&k.0(i)=bschd_b_(i)*totalbenefit&k.; if "&opt"~="baseline" and &cyr.>2019 then benefit&k.0(i)=(bschd_t_(i)*adj_imp)*(totalbenefit&k.*(1-if_&opt.))+(rpschd_imp_( i)*((avgflow*wgt)/totalavgflow)*totalbenefit&k.); if "&opt"~="baseline" and &cyr.=2019 then benefit&k.0(i)=(bschd_i_(i)*adj_imp)*(totalbenefit&k.*if_&opt.)+(rpschd_imp_(i)*( (avgflow*wgt)/totalavgflow)*totalbenefit&k.);

benefit&k.3(i)=benefit&k.0(i)/(1.03**(i-1+(&t0.-&d0.)));

benefit&k.7(i)=benefit&k.0(i)/(1.07**(i-1+(&t0.-&d0.))); end; benefit&k.0_61=sum(of benefit&k.0_1-benefit&k.0_60); benefit&k.3_61=sum(of benefit&k.3_1-benefit&k.3_60); benefit&k.7_61=sum(of benefit&k.7_1-benefit&k.7_60); *this calculates the discounted benefits in each Page 18year of the compliance period; benefit&k.0_62=benefit&k.0_61/&yrsc.;

benefit&k.3_62=benefit&k.3_61*(.03*(1+.03)**(&yrsc.-1))/((1+.03)**&yrsc.-1);

benefit&k.7_62=benefit&k.7_61*(.07*(1+.07)**(&yrsc.-1))/((1+.07)**&yrsc.-1); benefit&k._peak=max(of benefit&k.0_1-benefit&k.0_60); *calculate the peak over the compliance period;

%mend LMH; %LMH(Li); %LMH(Mi); %LMH(Hi);

%macro EMH(k); array benefit&k.0(60) benefit&k.0_1-benefit&k.0_60; array benefit&k.3(60) benefit&k.3_1-benefit&k.3_60; array benefit&k.7(60) benefit&k.7_1-benefit&k.7_60;

do i=1 to 60; if "&opt"="baseline" then benefit&k.0(i)=bschd_b_(i)*totalbenefit&k.; if "&opt"~="baseline" and &cyr.>2019 then benefit&k.0(i)=(bschd_t_(i)*adj_ent)*(totalbenefit&k.*(1-if_&opt.))+(rpschd_ent_( i)*((avgflow*wgt)/totalavgflow)*totalbenefit&k.); if "&opt"~="baseline" and &cyr.=2019 then benefit&k.0(i)=0;

benefit&k.3(i)=benefit&k.0(i)/(1.03**(i-1+(&t0.-&d0.)));

benefit&k.7(i)=benefit&k.0(i)/(1.07**(i-1+(&t0.-&d0.))); end; benefit&k.0_61=sum(of benefit&k.0_1-benefit&k.0_60); benefit&k.3_61=sum(of benefit&k.3_1-benefit&k.3_60); benefit&k.7_61=sum(of benefit&k.7_1-benefit&k.7_60); *this calculates the discounted benefits in each year of the compliance period; benefit&k.0_62=benefit&k.0_61/&yrsc.;

benefit&k.3_62=benefit&k.3_61*(.03*(1+.03)**(&yrsc.-1))/((1+.03)**&yrsc.-1);

benefit&k.7_62=benefit&k.7_61*(.07*(1+.07)**(&yrsc.-1))/((1+.07)**&yrsc.-1); benefit&k._peak=max(of benefit&k.0_1-benefit&k.0_60); *calculate the peak over the compliance period; %mend EMH; %EMH(Le); %EMH(Me); %EMH(He);

benefitL_peak= benefitLI_peak + benefitLE_peak; benefitM_peak= benefitMI_peak + benefitME_peak; benefitH_peak= benefitHI_peak + benefitHE_peak;

totalbenefitL = totalbenefitLI + totalbenefitLE; totalbenefitM = totalbenefitMI + totalbenefitME; totalbenefitH = totalbenefitHI + totalbenefitHE;

benefitL0_61 = benefitLI0_61 + benefitLE0_61; benefitM0_61 = benefitMI0_61 + benefitME0_61; benefitH0_61 = benefitHI0_61 + benefitHE0_61;

benefitL3_61 = benefitLI3_61 + benefitLE3_61; benefitM3_61 = benefitMI3_61 + benefitME3_61; benefitH3_61 = benefitHI3_61 + benefitHE3_61;

benefitL7_61 = benefitLI7_61 + benefitLE7_61;

Page 19benefitM7_61 = benefitMI7_61 + benefitME7_61; benefitH7_61 = benefitHI7_61 + benefitHE7_61;

benefitL0_62 = benefitLI0_62 + benefitLE0_62; benefitM0_62 = benefitMI0_62 + benefitME0_62; benefitH0_62 = benefitHI0_62 + benefitHE0_62;

benefitL3_62 = benefitLI3_62 + benefitLE3_62; benefitM3_62 = benefitMI3_62 + benefitME3_62; benefitH3_62 = benefitHI3_62 + benefitHE3_62;

benefitL7_62 = benefitLI7_62 + benefitLE7_62; benefitM7_62 = benefitMI7_62 + benefitME7_62; benefitH7_62 = benefitHI7_62 + benefitHE7_62;

run;

%macro loopsc;

%do i=1 %to 59;

sum(bschd_&i) as bschd_&i, %end; sum(bschd_60) as bschd_60 %mend loopsc;

proc sql;

create table REC_&opt.sched as select

region, %loopsc from REC_&opt.FULL group by region; quit; %export(REC_&opt.sched,"&outloc.&output",&opt.sched);

* 7j5 summarize data by region; proc sql;

create table REC_&opt.R as select

region, mean(totalbenefitL) as totalbenefitL format=dollar30.0, mean(totalbenefitM) as totalbenefitM format=dollar30.0, mean(totalbenefitH) as totalbenefitH format=dollar30.0, sum(avgflow*&weight.) as avgflow format=comma20.0,

sum(benefitL0_61) as totL0 format=dollar30.0, sum(benefitL3_61) as totL3 format=dollar30.0, sum(benefitL7_61) as totL7 format=dollar30.0, sum(benefitL0_62) as annL0 format=dollar30.0, sum(benefitL3_62) as annL3 format=dollar30.0, sum(benefitL7_62) as annL7 format=dollar30.0,

sum(benefitM0_61) as totM0 format=dollar30.0, sum(benefitM3_61) as totM3 format=dollar30.0, sum(benefitM7_61) as totM7 format=dollar30.0, sum(benefitM0_62) as annM0 format=dollar30.0, sum(benefitM3_62) as annM3 format=dollar30.0, sum(benefitM7_62) as annM7 format=dollar30.0,

sum(benefitH0_61) as totH0 format=dollar30.0, sum(benefitH3_61) as totH3 format=dollar30.0, sum(benefitH7_61) as totH7 format=dollar30.0, sum(benefitH0_62) as annH0 format=dollar30.0, sum(benefitH3_62) as annH3 format=dollar30.0, sum(benefitH7_62) as annH7 format=dollar30.0, /*********************************************************/

sum(benefitLI0_61) as totLI0 format=dollar30.0, sum(benefitLI3_61) as totLI3 format=dollar30.0, sum(benefitLI7_61) as totLI7 format=dollar30.0, sum(benefitLI0_62) as annLI0 format=dollar30.0, sum(benefitLI3_62) as annLI3 format=dollar30.0, sum(benefitLI7_62) as annLI7 format=dollar30.0,

Page 20sum(benefitMI0_61) as totMI0 format=dollar30.0, sum(benefitMI3_61) as totMI3 format=dollar30.0, sum(benefitMI7_61) as totMI7 format=dollar30.0, sum(benefitMI0_62) as annMI0 format=dollar30.0, sum(benefitMI3_62) as annMI3 format=dollar30.0, sum(benefitMI7_62) as annMI7 format=dollar30.0,

sum(benefitHI0_61) as totHI0 format=dollar30.0, sum(benefitHI3_61) as totHI3 format=dollar30.0, sum(benefitHI7_61) as totHI7 format=dollar30.0, sum(benefitHI0_62) as annHI0 format=dollar30.0, sum(benefitHI3_62) as annHI3 format=dollar30.0, sum(benefitHI7_62) as annHI7 format=dollar30.0, /*********************************************************/

sum(benefitLE0_61) as totLE0 format=dollar30.0, sum(benefitLE3_61) as totLE3 format=dollar30.0, sum(benefitLE7_61) as totLE7 format=dollar30.0, sum(benefitLE0_62) as annLE0 format=dollar30.0, sum(benefitLE3_62) as annLE3 format=dollar30.0, sum(benefitLE7_62) as annLE7 format=dollar30.0,

sum(benefitME0_61) as totME0 format=dollar30.0, sum(benefitME3_61) as totME3 format=dollar30.0, sum(benefitME7_61) as totME7 format=dollar30.0, sum(benefitME0_62) as annME0 format=dollar30.0, sum(benefitME3_62) as annME3 format=dollar30.0, sum(benefitME7_62) as annME7 format=dollar30.0,

sum(benefitHE0_61) as totHE0 format=dollar30.0, sum(benefitHE3_61) as totHE3 format=dollar30.0, sum(benefitHE7_61) as totHE7 format=dollar30.0, sum(benefitHE0_62) as annHE0 format=dollar30.0, sum(benefitHE3_62) as annHE3 format=dollar30.0, sum(benefitHE7_62) as annHE7 format=dollar30.0,

/*********************************************************/

sum(benefitL_peak) as benefitL_peak format=dollar30.0, sum(benefitM_peak) as benefitM_peak format=dollar30.0, sum(benefitH_peak) as benefitH_peak format=dollar30.0,

sum(benefitLi_peak) as benefitLi_peak format=dollar30.0, sum(benefitMi_peak) as benefitMi_peak format=dollar30.0, sum(benefitHi_peak) as benefitHi_peak format=dollar30.0,

sum(benefitLe_peak) as benefitLe_peak format=dollar30.0, sum(benefitMe_peak) as benefitMe_peak format=dollar30.0, sum(benefitHe_peak) as benefitHe_peak format=dollar30.0 /*********************************************************/

from REC_&opt.FULL group by region; quit; %mend benbyoption; %roptionmac2;

%export(Byspecies_baseline,"&outloc.&output",Byspecies_baseline);run;

* (8) Reshape Data into the format of the tables for exporting; * 8a Reshape Data into the format of the tables for exporting (region, species-specific);

%let regionlist= California^North Atlantic^Mid-Atlantic^South Atlantic^Gulf of Mexico^Great Lakes^Inland; %macro numbertables;

%do j=1 %to 7; *7 regions;

%do k=1 %to &speciestabs; *4 options;

%let i=%eval((&j-1)*&nby+(&k-1)*2+&firsttable+&j.-1); data Table_R_&i. (keep=printspecies fish_i fish_e fish_ie ); Page 21set TByspecies_%scan(&optionlist,&k,^); if "%scan(®ionlist,&j,^)"=Region;

run; data T_&i;

retain printspecies fish_i fish_e fish_ie tag; length tag $40.; set Table_R_&i.;

tag="T_&i."||printspecies||"%scan(&optionlist,&k)"; label printspecies="Species Name"

fish_i="Reduced I" fish_e="Reduced E" fish_ie='Reduced I&E'; format fish_i 20.; format fish_e 20.; format fish_ie 20.;

run; %end;

%end; %mend; %numbertables;

* 8b Reshape Data into the format of the tables for exporting (region, species group-specific); %macro dollartables;

%do j=1 %to 7; *7 regions;

%do k=1 %to &dollartabs; *5 options;

%let i=%eval((&j-1)*&nby+(&k-1)*2+&firsttable+1+&j.-1); data g_i (keep=type Pspecies matequivs LP MP HP LT MT HT); set Byspecies_%scan(&optionlist,&k,^) (drop=matequivs); if "%scan(®ionlist,&j,^)"=Region; if substr(printspecies,1,7)= "Total ("; format pspecies $50.; if group~="Total" then Pspecies=propcase(group); else Pspecies="Total (Undiscounted)"; type="I "; LT=totalbenefitLi; MT=totalbenefitMi; HT=totalbenefitHi; matequivs=matequivsi; format matequivs 20.; format matequivsi 20.;

run;

data g_e (keep=type Pspecies matequivs LP MP HP LT MT HT); set Byspecies_%scan(&optionlist,&k,^) (drop=matequivs);

if "%scan(®ionlist,&j,^)"=Region; if substr(printspecies,1,7)="Total ("; format pspecies $50.; if group~="Total" then Pspecies=propcase(group); else Pspecies="Total (Undiscounted)"; type="E "; LT=totalbenefitLe; MT=totalbenefitMe; HT=totalbenefitHe; matequivs=matequivse; format matequivs 20.; format matequivse 20.;

run;

Page 22data g_ie (keep=type Pspecies matequivs LP MP HP LT MT

HT); set Byspecies_%scan(&optionlist,&k,^); if "%scan(®ionlist,&j,^)"=Region; if substr(printspecies,1,7)="Total ("; format pspecies $50.; if group~="Total" then Pspecies=propcase(group); else Pspecies="Total (Undiscounted)"; type="IE"; LT=totalbenefitL; MT=totalbenefitM; HT=totalbenefitH;

run;

%macro typerate(type,rate); data t&rate._&type. (keep=type Pspecies LP MP HP LT MT HT); length type $4.;

set rec_%scan(&optionlist,&k,^)r; if "%scan(®ionlist,&j,^)"=Region; format pspecies $50.;

LT=annL&type.&rate.;

MT=annM&type.&rate.;

HT=annH&type.&rate.; format pspecies $50.; Pspecies="Total (&rate.% Discount Rate)";
LP=.; MP=.; HP=.; type="&type.";

run; %mend; %typerate(I,3); %typerate(E,3); %typerate(I,7); %typerate(E,7);

data t3_ie (keep=type Pspecies LP MP HP LT MT HT);

set rec_%scan(&optionlist,&k,^)r;

if "%scan(®ionlist,&j,^)"=Region; format pspecies $50.; LT=annL3; MT=annM3; HT=annH3; format pspecies $50.; Pspecies="Total (3% Discount Rate)";
LP=.; MP=.; HP=.; type="IE";

run;

data t7_ie (keep=type Pspecies LP MP HP LT MT HT);

set rec_%scan(&optionlist,&k,^)r;

if "%scan(®ionlist,&j,^)"=Region; format pspecies $50.; LT=annL7; MT=annM7; HT=annH7; format pspecies $50.; Pspecies="Total (7% Discount Rate)";
LP=.; MP=.; HP=.; type="IE";

run; Page 23data i;set t7_ie; format pspecies $50.;
LT=.; MT=.; HT=.; format pspecies $50.; Pspecies="blank row";
LP=.; MP=.; HP=.; type="I ";

run;

data E;set t7_ie;

format pspecies $50.;
LT=.; MT=.; HT=.; format pspecies $50.; Pspecies="blank row";
LP=.; MP=.; HP=.; type="E ";

run;

data IE;set t7_ie;

format pspecies $50.;
LT=.; MT=.; HT=.; format pspecies $50.; Pspecies="blank row";
LP=.; MP=.; HP=.; type="IE";

run;

data T_&i. (keep=tag type Pspecies matequivs LP MP HP LT MT HT region); retain tag type Pspecies matequivs LP MP HP LT MT HT region; length type $2. Tag $80.;

set I g_i t3_i t7_i

E g_e t3_e t7_e IE g_ie t3_ie t7_ie; format LT MT HT 20.0; format matequivs 20.0; label matequivs="Annual Reduction in # of Fish Lost"; label LP="Low WTP" MP="Mean WTP" HP="High WTP" LT="Low Benefit" MT="Mean Benefit" HT="High Benefit"; if &j=1 then Region="CA"; else if &j=2 then Region="NA"; else if &j=3 then Region="MA"; else if &j=4 then Region="SA"; else if &j=5 then Region="GM"; else if &j=6 then Region="GL"; else if &j=7 then Region="IN" ;

Tag="T_&i._%scan(&optionlist,&k,^)"||trim(type)||trim(Pspecies)||trim(Region); if Pspecies="Total (Undiscounted)" then x=matequivs; else X+matequivs; if Pspecies="Total (7% Discount Rate)" | Pspecies="Total (3% Discount Rate)" then matequivs=x; Page 24LT=LT/1000; *Thousands; MT=MT/1000; HT=HT/1000;

run; %end; %end; %mend dollartables;

%dollartables;

%macro national;

%let j=%eval(&firsttable+(&speciestabs+&dollartabs)*7-1+7); *=72;

%do o=1 %to &nattabs;

PROC DATASETS LIB=work nolist; DELETE n0; RUN; quit; %do r=1 %to 7; *7 regions;

%let i=%eval((&r-1)*&nby+(&o-1)*2+&firsttable+1+&r.-1);

proc append base=n0 data=T_&i;

run; %end;

*5 options;

data n1;length REGION $20.;

set n0;if type="I" then sort1=1;

else if type="E" then sort1=2; else sort1=3;

if Pspecies="Total (Undiscounted)" then sort2=1; else if Pspecies="Total (3% Discount Rate)" then sort2=3; else if Pspecies="Total (7% Discount Rate)" then sort2=5; else if region="CA" and Pspecies="blank row" then sort2=0; else if region="NA" and Pspecies="blank row" then sort2=2; else if region="MA" and Pspecies="blank row" then sort2=4; else delete;

if region="CA" then sort3=1; else if region="NA" then sort3=2; else if region="MA" then sort3=3; else if region="SA" then sort3=4; else if region="GM" then sort3=5; else if region="GL" then sort3=6; else if region="IN" then sort3=7;

if region="CA" and Pspecies~="blank row" then REGION="California"; else if region="NA" and Pspecies~="blank row" then REGION="North Atlantic"; else if region="MA" and Pspecies~="blank row" then REGION="Mid-Atlantic"; else if region="GM" and Pspecies~="blank row" then REGION="Gulf of Mexico"; else if region="GL" and Pspecies~="blank row" then REGION="Great Lakes"; else if region="IN" and Pspecies~="blank row" then REGION="Inland"; Page 25else if region="SA" and Pspecies~="blank row"

then REGION="South Atlantic"; else region="blank row";

if matequivs>0 then do;

LP=LT/matequivs*1000; MP=MT/matequivs*1000; HP=HT/matequivs*1000; end;

run;

proc sql;

create table n3 as select

type,Pspecies, "Total" as region format=$30., mean(sort1) as sort1,

mean(sort2) as sort2, 8 as sort3, sum(matequivs) as matequivs format= 20., sum(LT)/sum(matequivs)*1000 as LP format= 20.2, sum(MT)/sum(matequivs)*1000 as MP format= 20.2, sum(HT)/sum(matequivs)*1000 as HP format= 20.2, sum(LT) as LT

format= 30.0, sum(MT) as MT format= 30.0, sum(HT) as HT format= 30.0 from n1where Pspecies~="blank row" group by type,Pspecies order by sort1,sort2; quit;

data n4;length REGION $20.;

set n3 n1;

run;

proc sort data=n4 out=n5; by sort1 sort2 sort3; run;

%let t1=%eval(&j+ (&o-1)*3+1); %let t2=%eval(&j+ (&o-1)*3+2); %let t3=%eval(&j+ (&o-1)*3+3);

data T_&t1. (keep= region matequivs LP MP HP LT MT HT discount tag) T_&t2. (keep= region matequivs LP MP HP LT MT HT discount tag) T_&t3. (keep= region matequivs LP MP HP LT MT HT discount tag); retain region matequivs LP MP HP LT MT HT tag discount; length tag $30.;

set n5;label

discount="Discounting" LP="Low WTP" MP="Mean WTP" HP="High WTP" LT="Low Benefit" MT="Mean Benefit" HT="High Benefit"; if pspecies="Total (Undiscounted)" Page 26then discount="Undiscounted"; else if pspecies="Total (3% Discount

Rate)" then discount="3% Discount"; else if pspecies="Total (7% Discount Rate)" then discount="7% Discount"; else discount="blank"; if sort2~=0; *FLAG! FLAG! FLAG! FLAG! FLAG! ; if sort2=2 then discount="3% Discount"; if sort2=4 then discount="7% Discount"; if type="I" then do;

tag="Table_r_&t1._I_Nat_%scan(&optionlist,&o,^)"; output T_&t1.; end; else if type="E" then do;

tag="Table_r_&t2._E_Nat_%scan(&optionlist,&o,^)"; output T_&t2.; end; else do;

tag="Table_r_&t3._IE_Nat_%scan(&optionlist,&o,^)"; output T_&t3.; end;

run; %end; %mend national; %national;

%macro osum; %let start=%eval(&firsttable+(&speciestabs+&dollartabs)*7+7);

%let finish=%eval(&start+&nattabs*3-1);

%do j=&start %to &finish;

proc append base=osum data=T_&j;

run; %end; %mend; %osum;

data d3 (keep=option region matequivs LT3 MT3 HT3);

set osum;

if region~="blank row"; if substr(tag,13,2)="IE"; option=substr(tag,20,2); if discount="3% Discount"; matequivs=matequivs/1000; rename LT=LT3 mT=mT3 hT=hT3;

run;

data d7 (keep=option region LT7 MT7 HT7);

set osum;

if region~="blank row"; if substr(tag,13,2)="IE"; option=substr(tag,20,2); if discount="7% Discount"; matequivs=matequivs/1000; rename LT=LT7 mT=mT7 hT=hT7;

run; proc sort data=d3; by region option; run; proc sort data=d7; by region option; run; data osum1;

merge d3 d7;

by region option; if region~="Total"; Page 27if option="tmn1" then sort2=1; else if option="tmn2" then sort2=2; else if option="tmn3" then sort2=3;

*California^North Atlantic^Mid-Atlantic^South Atlantic^Gulf of Mexico^Great Lakes^Inland; if region="California" then sort1=1;

else if region="North Atlantic" then sort1=2; else if region="Mid-Atlantic" then sort1=3; else if region="South Atlantic" then sort1=4;

run; proc sort data=osum1; by sort1 sort2; run;

* 10 export all the tables; %macro exportall;

%let end=%eval(&firsttable+(&speciestabs+&dollartabs)*7+&nattabs*3-1); %do k=&firsttable %to &end;

%export(T_&k.,"&outloc.&Tables",T_&k.); %end; %mend exportall;

* Generate rtf output; %let Regionlist=

CA

NA

MA

SA

GM

GL

IN

;

%macro printit2(id); %let tabs=%eval((&speciestabs+&dollartabs)/2);

%do r=1 %to 7;

%do t=1 %to &tabs;

%let i=%eval((&r-1)*&nby+(&t-1)*2+&firsttable+&r-1);

Title "Table &i.: Recreational I+E Reductions by Species: %scan(&Optionnames,&t,^), %scan(®ionlist,&r)"; proc print data=t_&i label noobs; var &id. printspecies fish_i fish_e fish_ie;

run; %let i=%eval((&r-1)*&nby+(&t-1)*2+&firsttable+1+&r-1); %let xx=%eval(&i+1);

Title "Table &i.: Recreational I+E Reductions by Region: %scan(&Optionnames,&t,^), %scan(®ionlist,&r)"; proc print data=t_&i label noobs; var &id. pspecies matequivs LP MP
HP LT MT HT; Label pspecies="Species Group";

run; %if &t=&tabs %then %do;

Title "Table &xx.: Options Summary by Region: %scan(®ionlist,&r)"; proc print data=osum1 noobs label; var option matequivs lt3 mt3 ht3 lt7 mt7 ht7; label matequivs="Rec Losses (thousands)" lt3="3 percent low rec ben (thousands)" mt3="3 percent mean rec ben (thousands)" ht3="3 percent high rec ben (thousands)" lt7="7 percent Page 28low rec ben (thousands)" mt7="7 percent

mean rec ben (thousands)" ht7="7 percent high rec ben (thousands)"; where sort1=&r;

run; %end; %end; %end; %mend;

%macro printit3(id); %let type=Impingement Entrainment IE;

%do o=1 %to &nattabs;

%do t=1 %to 3;

%let i=%eval((&o-1)*3 + &t + &firsttable+(&speciestabs+&dollartabs)*7-1); Title "Table &i: National Benefits (%scan(&type,&t)) : %scan(&Optionnames,&o,^)"; proc print data=t_&i label noobs;

var &id. discount region matequivs LP MP HP LT MT
HT; where discount~="Undiscounted";

run; %end; %end; %mend printit3;

*ods rtf body="&printedoutput."; *%printit2(); *ods rtf close;

ods csvall body="&printedoutput3."; %printit2(tag); ods csvall close;

%macro national2sum;

PROC DATASETS LIB=work nolist; DELETE nat0; RUN; quit; %do o=1 %to &nattabs;

%do t=1 %to 3;

%let i=%eval((&o-1)*3 + &t + &firsttable+(&speciestabs+&dollartabs)*7-1+7); %if &t=3 %then %do;

proc append base=nat0 data=T_&i;

run; %end; %end; %end; %mend national2sum; %national2sum;

*EXPORT RESULTS FOR TMN1*******************************************************; data disc_out_tmn1;

set Rec_tmn1full (keep= region benefitMi0_1-benefitMi0_60 benefitMe0_1-benefitMe0_60 benefitMi3_1-benefitMi3_60 benefitMe3_1-benefitMe3_60 benefitMi7_1-benefitMi7_60 benefitMe7_1-benefitMe7_60

totalbenefitM benefitM0_61 benefitM0_62 benefitM3_61 benefitM3_62 benefitM7_61 benefitM7_62);

*_61 is the NPV, and _62 is the annualized value; array benefitMi0_(60) benefitMi0_1-benefitMi0_60; array benefitMi3_(60) benefitMi3_1-benefitMi3_60; array benefitMi7_(60) benefitMi7_1-benefitMi7_60; array benefitMe0_(60) benefitMe0_1-benefitMe0_60; array benefitMe3_(60) benefitMe3_1-benefitMe3_60; array benefitMe7_(60) benefitMe7_1-benefitMe7_60; Page 29array benefitM0_(60) benefitM0_1-benefitM0_60; array benefitM3_(60) benefitM3_1-benefitM3_60; array benefitM7_(60) benefitM7_1-benefitM7_60; do i=1 to 60; benefitM0_(i) = benefitMi0_(i) + benefitMe0_(i); benefitM3_(i) = benefitMi3_(i) + benefitMe3_(i); benefitM7_(i) = benefitMi7_(i) + benefitMe7_(i);
END; drop i totalbenefitM benefitMi0_1-benefitMi0_60 benefitMe0_1-benefitMe0_60 benefitMi3_1-benefitMi3_60 benefitMe3_1-benefitMe3_60 benefitMi7_1-benefitMi7_60 benefitMe7_1-benefitMe7_60;
RUN;

*start separate calculations for different discount rates;

Proc transpose data=disc_out_tmn1 out=disc_out_tmn1;

by region;
RUN; Data disc_out_tmn1;

set disc_out_tmn1 (rename=(_NAME_=ben_yr)); reg_ben=sum(of col1-col392); drop col1-col397; DO year=substr(ben_yr,11);
END; RUN; Data disc_out_tmn1;

length ben_yr $ 9; Set disc_out_tmn1; year_num=input(year,2.); drop year;
RUN; DATA disc_out_tmn1;

set disc_out_tmn1 (rename=(year_num=year)); drop year_num;
RUN; proc sql;

create table disc_out_tmn1_nat as select

ben_yr,

year, sum(reg_ben)/1000 as ben_tot from disc_out_tmn1 group by ben_yr, year; quit;

%export(disc_out_tmn1,"&outloc.&output",disc_out_tmn1); %export(disc_out_tmn1_nat,"&outloc.&output",disc_out_tmn1_nat);

*EXPORT RESULTS FOR TMN2*******************************************************;

data disc_out_tmn2;

set Rec_tmn2full (keep= region benefitMi0_1-benefitMi0_60 benefitMe0_1-benefitMe0_60 benefitMi3_1-benefitMi3_60 benefitMe3_1-benefitMe3_60 benefitMi7_1-benefitMi7_60 benefitMe7_1-benefitMe7_60

totalbenefitM benefitM0_61 benefitM0_62 benefitM3_61 benefitM3_62 benefitM7_61 benefitM7_62);

*_61 is the NPV, and _62 is the annualized value; array benefitMi0_(60) benefitMi0_1-benefitMi0_60; array benefitMi3_(60) benefitMi3_1-benefitMi3_60; array benefitMi7_(60) benefitMi7_1-benefitMi7_60; array benefitMe0_(60) benefitMe0_1-benefitMe0_60; array benefitMe3_(60) benefitMe3_1-benefitMe3_60; array benefitMe7_(60) benefitMe7_1-benefitMe7_60; array benefitM0_(60) benefitM0_1-benefitM0_60; array benefitM3_(60) benefitM3_1-benefitM3_60; array benefitM7_(60) benefitM7_1-benefitM7_60; do i=1 to 60; benefitM0_(i) = benefitMi0_(i) + benefitMe0_(i); benefitM3_(i) = benefitMi3_(i) + benefitMe3_(i); benefitM7_(i) = benefitMi7_(i) + benefitMe7_(i); Page 30END; drop i totalbenefitM benefitMi0_1-benefitMi0_60 benefitMe0_1-benefitMe0_60 benefitMi3_1-benefitMi3_60 benefitMe3_1-benefitMe3_60 benefitMi7_1-benefitMi7_60 benefitMe7_1-benefitMe7_60;
RUN;

*start separate calculations for different discount rates;

Proc transpose data=disc_out_tmn2 out=disc_out_tmn2;

by region;
RUN; Data disc_out_tmn2;

set disc_out_tmn2 (rename=(_NAME_=ben_yr)); reg_ben=sum(of col1-col392); drop col1-col397; DO year=substr(ben_yr,11);
END; RUN; Data disc_out_tmn2;

length ben_yr $ 9; Set disc_out_tmn2; year_num=input(year,2.); drop year;
RUN; DATA disc_out_tmn2;

set disc_out_tmn2 (rename=(year_num=year)); drop year_num;
RUN; proc sql;

create table disc_out_tmn2_nat as select

ben_yr,

year, sum(reg_ben)/1000 as ben_tot from disc_out_tmn2 group by ben_yr, year; quit;

%export(disc_out_tmn2,"&outloc.&output",disc_out_tmn2); %export(disc_out_tmn2_nat,"&outloc.&output",disc_out_tmn2_nat);

*EXPORT RESULTS FOR TMN3*******************************************************;

data disc_out_tmn3;

set Rec_tmn3full (keep= region benefitMi0_1-benefitMi0_60 benefitMe0_1-benefitMe0_60 benefitMi3_1-benefitMi3_60 benefitMe3_1-benefitMe3_60 benefitMi7_1-benefitMi7_60 benefitMe7_1-benefitMe7_60

totalbenefitM benefitM0_61 benefitM0_62 benefitM3_61 benefitM3_62 benefitM7_61 benefitM7_62);

*_61 is the NPV, and _62 is the annualized value; array benefitMi0_(60) benefitMi0_1-benefitMi0_60; array benefitMi3_(60) benefitMi3_1-benefitMi3_60; array benefitMi7_(60) benefitMi7_1-benefitMi7_60; array benefitMe0_(60) benefitMe0_1-benefitMe0_60; array benefitMe3_(60) benefitMe3_1-benefitMe3_60; array benefitMe7_(60) benefitMe7_1-benefitMe7_60; array benefitM0_(60) benefitM0_1-benefitM0_60; array benefitM3_(60) benefitM3_1-benefitM3_60; array benefitM7_(60) benefitM7_1-benefitM7_60; do i=1 to 60; benefitM0_(i) = benefitMi0_(i) + benefitMe0_(i); benefitM3_(i) = benefitMi3_(i) + benefitMe3_(i); benefitM7_(i) = benefitMi7_(i) + benefitMe7_(i);
END; drop i totalbenefitM benefitMi0_1-benefitMi0_60 benefitMe0_1-benefitMe0_60 benefitMi3_1-benefitMi3_60 benefitMe3_1-benefitMe3_60 benefitMi7_1-benefitMi7_60 benefitMe7_1-benefitMe7_60;
RUN;

*start separate calculations for different discount rates; Page 31Proc transpose data=disc_out_tmn3 out=disc_out_tmn3; by region;
RUN; Data disc_out_tmn3;

set disc_out_tmn3 (rename=(_NAME_=ben_yr)); reg_ben=sum(of col1-col392); drop col1-col397; DO year=substr(ben_yr,11);
END; RUN; Data disc_out_tmn3;

length ben_yr $ 9; Set disc_out_tmn3; year_num=input(year,2.); drop year;
RUN; DATA disc_out_tmn3;

set disc_out_tmn3 (rename=(year_num=year)); drop year_num;
RUN; proc sql;

create table disc_out_tmn3_nat as select

ben_yr,

year, sum(reg_ben)/1000 as ben_tot from disc_out_tmn3 group by ben_yr, year; quit;

%export(disc_out_tmn3,"&outloc.&output",disc_out_tmn3); %export(disc_out_tmn3_nat,"&outloc.&output",disc_out_tmn3_nat);

%macro summary(opt); Data rec_&opt.r_out; set rec_&opt.r; option="&opt.";
RUN; Data rec_&opt.r_out2; set rec_&opt.r_out; keep region option annL3 annL7 annM3 annM7 annH3 annH7 annL0 annM0 annH0 benefitL_peak benefitM_peak benefitH_peak benefitLi_peak benefitMi_peak benefitHi_peak benefitLe_peak benefitMe_peak benefitHe_peak

; RUN; %mend; %summary(baseline); %summary(tmn1); %summary(tmn2); %summary(tmn3);

Data rec_summary_out;

set rec_baseliner_out2 rec_tmn1r_out2 rec_tmn2r_out2 rec_tmn3r_out2;
RUN; %export(rec_summary_out,"&outloc.&output",rec_summary_out);

Page 32