Rec_03.21.11.txt

Authors: Abt Associates Inc. Created: 1/31/2008 Updated: 3/22/2011 Purpose: This is a SAS program for recreational fishing benefits associated with the proposed 316(b) existing facilities regulation;

*!! Benefits are reported in $1,000.

*The location of the input and output files is here;

%let d=h; *drive (c or h); %let mainloc=&d.:\ERD\TYCHE Contract\Assignments\WA 2-15 (316b Benefits & SP Study)\Task 5 - Benefits Analytic Support\IE_032111\Rec\;

%let inloc=&mainloc.Inputs\; %let outloc=&mainloc.Outputs\; libname out "&outloc."; *The name of the input file is;

%let input=Rec_Inputs_03.21.11.xls; %let input2=IE_outputs_22MAR11.xls; %let insheeti=BNB_IMP; %let insheete=BNB_ENT; %let insheetie=BNB_IE; %let insheetrd=REDUCTIONS_DETAIL; %let insheetrpschd=rpschd;

*The name of the output files are;

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

%let t0=2012; *(define when first year of compliance is); %let yrsc=50; *(define number of compliance years); %let ieunits=1; *units of ie data; *Dollars are in 2008$ (after adjustment);

%macro cyr; cydata.TMN1 as cyr_base, cydata.TMN1 as cyr_TMN1, cydata.TMN2 as cyr_TMN2, cydata.TMN3 as cyr_TMN3, cydata.TMN4 as cyr_TMN4 %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"; else if UPCASE(option_name)=UPCASE("option.TMN4") then option="TMN4";

end; if &code=2 then do; *SELECT baseline option;

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

end; %mend;

Page 1Rec_03.21.11.txt

%macro roptionmac1; %optionspecific(baseline,baseline,rec_loss); %optionspecific(TMN1,TMN1,rec_loss); %optionspecific(TMN2,TMN2,rec_loss); %optionspecific(TMN3,TMN3,rec_loss); %optionspecific(TMN4,TMN4,rec_loss); %mend;

%macro roptionmac2; %benbyoption(baseline,baseline,NewWeight,cyr_base); %benbyoption(rule,TMN1,NewWeight,cyr_TMN1); %benbyoption(rule,TMN2,NewWeight,cyr_TMN2); %benbyoption(rule,TMN3,NewWeight,cyr_TMN3); %benbyoption(rule,TMN4,NewWeight,cyr_TMN4); %mend; %let optionlist=baseline^TMN1^TMN2^TMN3^TMN4; %let speciestabs=5; *number of options; %let dollartabs=5; *number of options; %let firsttable=1; %let nby=10; *number of tables under each option=2*options; %let nattabs=5; *number of options; %let Optionnames=baseline^TMN1^TMN2^TMN3^TMN4;

*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 I&E Data; * 1a Import I&E data;

%import(&inloc.&input2,&insheeti.,I); %import(&inloc.&input2,&insheete.,E); %import(&inloc.&input2,&insheetie.,ei); %import(&inloc.&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.; Page 2Rec_03.21.11.txt 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 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"; * and option_name ="red.opt.baseline"; %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 Page 3Rec_03.21.11.txt

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

*(2) Import and Format Fish Values Data; %import(&inloc.&input,Value_Per_FISH,value);

*(3) Import and format species map for mapping the species var in the I&E data w/ species var in fish value data; * 3a Import species map; %import(&inloc.&input,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 Page 4Rec_03.21.11.txt 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; data check2a;

set temp;

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

run;

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

* 4d Adjust for Inflation; %import(&inloc.&input,CPI.2002.2009,cpi1); data cpi2 (keep=oneis2009);

set cpi1;

if year=2003;

run;

data value3 (drop=oneis2009);

if _n_=1 then set cpi2; set value3a;

lp=lp*oneis2009; mp=mp*oneis2009; hp=hp*oneis2009;

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; Page 5Rec_03.21.11.txt

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; %export(checkvalue2,"&outloc.&output",checkvalue2); * (6) Generate Option Specific Total Benefit Files; * 6a Baseline (Eliminate all I&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; *delete;

*group ="other saltwater"; end;

if group="generic freshwater"

then do; group ="unidentified";

species ="unidentified"; end;

run;

proc sql;

create table value&name.IMP as select

region,option, sum(&lossvariable.)*&ieunits. as matequivsI format=comma20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLI format=dollar20.0,

sum(mp*&lossvariable.)*&ieunits. as totalbenefitMI format=dollar20.0,

sum(hp*&lossvariable.)*&ieunits. as totalbenefitHI format=dollar20.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=comma20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLE format=dollar20.0,

sum(mp*&lossvariable.)*&ieunits. as totalbenefitME format=dollar20.0,

sum(hp*&lossvariable.)*&ieunits. as totalbenefitHE format=dollar20.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=comma20.0, i.matequivsI format=comma20.0, e.matequivsE+i.matequivsI as matequivs format=comma20.0,

Page 6Rec_03.21.11.txt e.totalbenefitLE format=dollar20.0, i.totalbenefitLI format=dollar20.0, e.totalbenefitLE+i.totalbenefitLI as totalbenefitL format=dollar20.0,

e.totalbenefitME format=dollar20.0, i.totalbenefitMI format=dollar20.0, e.totalbenefitME+i.totalbenefitMI as totalbenefitM format=dollar20.0,

e.totalbenefitHE format=dollar20.0, i.totalbenefitHI format=dollar20.0, e.totalbenefitHE+i.totalbenefitHI as totalbenefitH format=dollar20.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=comma20.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=dollar20.0,

sum(mp*&lossvariable.)*&ieunits. as totalbenefitMI format=dollar20.0,

sum(hp*&lossvariable.)*&ieunits. as totalbenefitHI format=dollar20.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,

sum(&lossvariable.)*&ieunits. as matequivsE format=comma20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLE format=dollar20.0,

sum(mp*&lossvariable.)*&ieunits. as totalbenefitME format=dollar20.0,

sum(hp*&lossvariable.)*&ieunits. as totalbenefitHE format=dollar20.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, Page 7Rec_03.21.11.txt

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=comma20.0, i.matequivsI format=comma20.0, e.matequivsE+i.matequivsI as matequivs format=comma20.0,

e.totalbenefitLE format=dollar20.0, i.totalbenefitLI format=dollar20.0, e.totalbenefitLE+i.totalbenefitLI as totalbenefitL format=dollar20.0,

e.totalbenefitME format=dollar20.0, i.totalbenefitMI format=dollar20.0, e.totalbenefitME+i.totalbenefitMI as totalbenefitM format=dollar20.0,

e.totalbenefitHE format=dollar20.0, i.totalbenefitHI format=dollar20.0, e.totalbenefitHE+i.totalbenefitHI as totalbenefitH format=dollar20.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); totalbenefitH=sum(totalbenefitHI,totalbenefitHE); format matequivs matequivsI matequivsE comma20.0; format totalbenefitL totalbenefitM totalbenefitH totalbenefitLi totalbenefitMi Page 8Rec_03.21.11.txt

totalbenefitHi totalbenefitLe totalbenefitMe totalbenefitHe dollar20.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=comma20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLI format=dollar20.0,

sum(mp*&lossvariable.)*&ieunits. as totalbenefitMI format=dollar20.0,

sum(hp*&lossvariable.)*&ieunits. as totalbenefitHI format=dollar20.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=comma20.0, sum(lp*&lossvariable.)*&ieunits. as totalbenefitLE format=dollar20.0,

sum(mp*&lossvariable.)*&ieunits. as totalbenefitME format=dollar20.0,

sum(hp*&lossvariable.)*&ieunits. as totalbenefitHE format=dollar20.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=comma20.0,

i.matequivsI format=comma20.0, e.matequivsE+i.matequivsI as matequivs format=comma20.0,

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

Page 9Rec_03.21.11.txt e.totalbenefitLE format=dollar20.0, i.totalbenefitLI format=dollar20.0, e.totalbenefitLE+i.totalbenefitLI as totalbenefitL format=dollar20.0,

e.totalbenefitME format=dollar20.0, i.totalbenefitMI format=dollar20.0, e.totalbenefitME+i.totalbenefitMI as totalbenefitM format=dollar20.0,

e.totalbenefitHE format=dollar20.0, i.totalbenefitHI format=dollar20.0, e.totalbenefitHE+i.totalbenefitHI as totalbenefitH format=dollar20.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 comma20.0; format totalbenefitL totalbenefitM totalbenefitH totalbenefitLi totalbenefitMi totalbenefitHi totalbenefitLe totalbenefitMe totalbenefitHe dollar20.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; Page 10Rec_03.21.11.txt 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 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 dollar7.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; Page 11Rec_03.21.11.txt 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;

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 comma30.0; /* fish_&name._i=matequivsi; fish_&name._e=matequivse; fish_&name._ie=matequivs; */ 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(&inloc.&input,IDMAP,f1); * 7b Import data with weights; %import(&inloc.&input,ID_AND_WEIGHT,ID_AND_WEIGHT); * 7c Import data with region and flow; %import(&inloc.&input,flowdata,f3); * 7d Import data with compliance dates; %import(&inloc.&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;Page 12Rec_03.21.11.txt

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

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,

%cyr from f8 full join cydata on cydata.id=f8.facility; quit;

*data checkxxx; * set s2a; * if cyr_t2=2012 and wgt~=. then put "flag 12345 " PLNTCODE; *run; **********************; **********************; * 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; Page 13Rec_03.21.11.txt 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); * 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 2010); 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<=45 then bio(i)=1; if i=46 then bio(i)=.9; if i=47 then bio(i)=.8; if i=48 then bio(i)=.2; if i=49 then bio(i)=.1; if i=50 then bio(i)=.05; if i>50 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; Page 14Rec_03.21.11.txt if i=7 then

bio(i)=1; if i>7 and i<=45 then bio(i)=1; if i=46 then bio(i)=.9; if i=47 then bio(i)=.8; if i=48 then bio(i)=.2; if i=49 then bio(i)=.1; if i=50 then bio(i)=.05; if i>50 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.; if _n_=1 then put "Not BASELINE";

if x<=0

then bio(i)=0; else if x=1 then bio(i)=.02; else if x=2 then bio(i)=.06; else if x=3 then bio(i)=.22; else if x=4 then bio(i)=.4; else if x=5 then bio(i)=.59; else if x=6 then bio(i)=.77; else if x=7 then bio(i)=.93; else if x=8 then bio(i)=.97; else if x=9 then bio(i)=.99; else if x>=9 and i<=45 then bio(i)=1; else if i=46 then bio(i)=.9; else if i=47 then bio(i)=.8; else if i=48 then Page 15Rec_03.21.11.txt

bio(i)=.2; else if i=49 then bio(i)=.1; else if i=50 then bio(i)=0.05; else if i>50 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.; 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)=.02; else if x=3 then bio(i)=.06; else if x=4 then bio(i)=.22; else if x=5 then bio(i)=.4; else if x=6 then bio(i)=.59; else if x=7 then bio(i)=.77; else if x=8 then bio(i)=.93; else if x=9 then bio(i)=.97; else if x=10 then bio(i)=.99; else if x>=10 and i<=45 then bio(i)=1; else if i=46 then bio(i)=.9; else if i=47 then bio(i)=.8; else if i=48 then bio(i)=.2; else if i=49 then bio(i)=.1; else if i=50 then bio(i)=0.05; else if i>50 then bio(i)=0;

end; end;

Page 16Rec_03.21.11.txt 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.) as totalavgflow from s4 group by region; quit;

*create separate regional flow total depending on whether the compliance year is 2013;

Data s5;set s5;

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

proc sql;

create table s5x as select s5.*, sum (avgflow_imp*&weight.) as totalavgflow_imp, sum (avgflow_tow*&weight.) 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 TO ACCOUNT FOR THE COMPLIANCE SCHEDULE!!!!!!!!!! *******************************************************************;

Data s5x;

Set s5x; if region="" then delete; if &cyr. = "." then delete;
RUN; Data reductions_detail_1;

Set reductions_detail; impingement_fraction=(1-ccr_fraction_imp); if option_name ="option.tmn1" then option_name="if_tmn1"; Page 17Rec_03.21.11.txt else if option_name="option.tmn2" then option_name="if_tmn2"; else if option_name="option.tmn3" then option_name="if_tmn3"; else if option_name="option.tmn4" then option_name="if_tmn4"; 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) rename=(col4=if_tmn4));

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; *if if_&opt.=1 then totalavgflow_imp=totalavgflow; *if totalavgflow_imp=0 then totalavgflow_tow=totalavgflow; *if totalavgflow_tow=0 then totalavgflow_imp=1;
RUN;

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

***

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

***

START OF REPOWERING CALCULATIONS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

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

***

************************************************************************************ ***;

%import(&inloc.&input,rpschd,rpschd); %import(&inloc.&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;

Page 18Rec_03.21.11.txt

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; if option="tmn4_0" then delete;
RUN;

Data rpschd;

set rpschd; if option="baseline_0" then delete; if option="tmn1_0" then delete; if option="tmn2_0" then delete; if option="tmn3_0" then delete; if option="tmn4_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"; if option="tmn4" then option="TMN4";
RUN;

***seperate repowering schedules/values for impingement and entrainment 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; Page 19Rec_03.21.11.txt 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;

by region;
RUN;

Data S5_1;

merge S5_1 repow_1imp repow_1ent; by region;
RUN;

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

**

************************************************************************************ ***;

*need to merge s5 with impingement fraction data;

* 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; *array checkit(60) checkit_1-checkit_60;

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

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

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

bschd_t_(i)=0; if &cyr.=2013 then bschd_t_(i)=0;

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

*checkit(i)=bschd_i_(i)*adj_imp+rpschd_imp_(i);

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.>2013 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.=2013 then benefit&k.0(i)=(bschd_i_(i)*adj_imp)*(totalbenefit&k.*if_&opt.)+(rpschd_imp_(i)*((av gflow*wgt)/totalavgflow)*totalbenefit&k.); *if "&opt"~="baseline" and &cyr.>2013 then benefit&k.0(i)=(bschd_t_(i)*adj_imp+rpschd_imp_(i))*(totalbenefit&k.*(1-if_&opt.)); *if "&opt"~="baseline" and &cyr.=2013 then benefit&k.0(i)=(bschd_i_(i)*adj_imp+rpschd_imp_(i))*(totalbenefit&k.*if_&opt.); benefit&k.3(i)=benefit&k.0(i)/(1.03**(i-1)); benefit&k.7(i)=benefit&k.0(i)/(1.07**(i-1)); 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+.03)**&yrsc.-1);

benefit&k.7_62=benefit&k.7_61*(.07*(1+.07)**&yrsc.)/((1+.07)**&yrsc.-1); %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.>2013 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.>2013 then benefit&k.0(i)=(bschd_t_(i)*adj_ent+rpschd_ent_(i))*(totalbenefit&k.*(1-if_&opt.)); if "&opt"~="baseline" and &cyr.=2013 then Page 21Rec_03.21.11.txt

benefit&k.0(i)=0;

benefit&k.3(i)=benefit&k.0(i)/(1.03**(i-1)); benefit&k.7(i)=benefit&k.0(i)/(1.07**(i-1)); 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+.03)**&yrsc.-1);

benefit&k.7_62=benefit&k.7_61*(.07*(1+.07)**&yrsc.)/((1+.07)**&yrsc.-1); %mend EMH; %EMH(Le); %EMH(Me); %EMH(He);

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; 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, Page 22Rec_03.21.11.txt %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,

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,

Page 23Rec_03.21.11.txt 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 /*********************************************************/

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^Gulf of Mexico^Great Lakes^Inland^South Atlantic; %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 );

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

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; Page 24Rec_03.21.11.txt %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;

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;

run;

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 Page 25Rec_03.21.11.txt

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

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;

Page 26Rec_03.21.11.txt 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 dollar20.0; format matequivs comma20.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_&n._&rname._&option."||type||Pspecies;

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

Page 27Rec_03.21.11.txt %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"; 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; Page 28Rec_03.21.11.txt

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=comma20., sum(LT)/sum(matequivs)*1000 as LP format=dollar20.2, sum(MT)/sum(matequivs)*1000 as MP format=dollar20.2, sum(HT)/sum(matequivs)*1000 as HP format=dollar20.2, sum(LT) as LT

format=dollar30.0, sum(MT) as MT format=dollar30.0, sum(HT) as HT format=dollar30.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)"

then discount="Undiscounted"; else if pspecies="Total (3% Discount Rate)" then discount="3% Discount"; Page 29Rec_03.21.11.txt 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; Page 30Rec_03.21.11.txt merge d3 d7;

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

*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; *%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. type pspecies matequivs LP
MP HP LT MT HT; var &id. pspecies matequivs LP MP HP
LT MT HT; Label pspecies="Species Group"; Page 31Rec_03.21.11.txt

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 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(); *%printit3(); ods rtf close;

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

%macro national2sum;

PROC DATASETS LIB=work nolist; DELETE nat0; RUN; quit; %do o=1 %to &nattabs; Page 32Rec_03.21.11.txt %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; data out.rec_bottomline (keep=region LT MT HT tag discount option); set nat0; option=substr(tag,19,12);

run;

data rec_bottomline ; set out.rec_bottomline;

run;

******************************************************************************* 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; 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-col392; DO year=substr(ben_yr,11);
END; RUN; Data disc_out_tmn1; Page 33Rec_03.21.11.txt 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);
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-col392; DO year=substr(ben_yr,11);
END; RUN; Page 34Rec_03.21.11.txt

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;

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-col392; DO year=substr(ben_yr,11);
END; Page 35Rec_03.21.11.txt

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

******************************************************************************* EXPORT RESULTS FOR TMN4; *******************************************************************************;

data disc_out_tmn4;

set Rec_tmn4full (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;

Proc transpose data=disc_out_tmn4 out=disc_out_tmn4;

by region;
RUN; Data disc_out_tmn4;

set disc_out_tmn4 (rename=(_NAME_=ben_yr)); reg_ben=sum(of col1-col392); drop col1-col392; DO year=substr(ben_yr,11);
END; Page 36Rec_03.21.11.txt

RUN; Data disc_out_tmn4;

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

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

create table disc_out_tmn4_nat as select

ben_yr,

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

%export(disc_out_tmn4,"&outloc.&output",disc_out_tmn4); %export(disc_out_tmn4_nat,"&outloc.&output",disc_out_tmn4_nat);

Page 37