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

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

*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 2
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.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 5
proc 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 6
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,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 7
totalbenefitH=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 8
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_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 9
totalbenefitLe 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 10
data 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 11
year;

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 12
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;

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 13
then 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 14
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;

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 15
Set 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 16
if 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 17
by 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 18
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 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 19
benefitM7_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 20
sum(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 21
set 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 22
data 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 23
data 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 24
LT=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 25
else 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 n1

where 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 26
then 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 27
if 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 28
low 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 29
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;

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 30
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_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 31
Proc 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