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

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

*SP Benefits Model_05.02.14;

*Author: Ryan Stapler, Abt Associates Inc.

*Created: 11/11/2011;

*Updated: 5/02/2014;

*Purpose: This is a SAS program which estimates benefits for the final rule and

other options considered based on the 316b stated preference survey. Note that

the resulting estimates are not included in EPA's cost-benefit comparison for the

final rule.

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

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

*SPECIFY INPUT AND OUTPUT FILES*************************************************;

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

%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\SP_survey\;

%let inloc=&mainloc.Inputs\;

%let outloc=&mainloc.Outputs\;

*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 ie_year=2017; *(all years prior to ie_year include installation of

impingement tech only. this is no longer relevant within the

code, set to

2017 to deactivate for model runs (the 1st compliance year is

2018));

%let af_3=(0.03*(1.03**(51-1)))/((1.03**51)-1); *(af_3 is the

annualization factor

to convert net present value of benefits to annualized benefits using a

3%

discount rate);

%let af_7=(0.07*(1.07**(51-1)))/((1.07**51)-1); *(af_7 is the

annualization factor

to convert net present value of benefits to annualized benefits using a

7%

discount rate);

*The name of the input file is;

%let fac_input=Facility_Inputs_and_Weights_01.25.14.xls;

%let input=SP_Survey_Benefits_Inputs_05.13.13.xls;

*%let input2=IE_outputs_22MAR11.xls;

%let input2=IE_outputs_25JAN14_adjusted_for_state_regs.xls;

%let input3=IE_outputs_20MAR14_for_SP_survey.xls; *--->no new run

necessary because baseline

was unchanged across model run - only baseline from this data set is

used;

*The name of the output files are;

%let output=316bsurvey_BenOutputs_&sysdate..xls;

%let tables=316bsurvey_BenTables_&sysdate..xls;

%let SumTables=316bsurvey_BenTablesSum_&sysdate..xls;

%let printout=&outloc.316bsurvey Ben Tables &sysdate..rtf;

%let printout3=&outloc.316bsurvey Ben Tables &sysdate..csv;

*DEFINE IMPORT AND EXPORT MACROS************************************************;

*This is a macro that can be used to import data from excel;

%macro import(infile,insheet,out);

proc import dbms=excelcs

datafile="&infile"

out=WORK.&out replace;

sheet="&insheet.$";

run;

%mend;

*This is a macro that can be used to export data to excel;

%macro export(datafile,outfile,sheet);

PROC EXPORT DATA= &datafile

Page 1
OUTFILE= &outfile

label

DBMS=excelcs replace;

SHEET=&sheet;

RUN;

%mend;

*Import IM&E data from the excel input sheets;

*import data for impingement and entrainment combined;

%import(&facloc.&input2,BNB_IE,ie);

*import impingement data;

%import(&facloc.&input2,BNB_IMP,i_only);

*import entrainment data;

%import(&facloc.&input2,BNB_ENT,ent_only);

*import the worksheet with the ccr.fraction data;

%import(&facloc.&input2,REDUCTIONS_DETAIL,reductions_detail);

*CALCULATE IMPINGEMENT FRACTION*******************************************

this section reorganizes the data to calcualte the portion of IM&E

attributable to installation of "impingement only" technology.

Note: this distinction is no longer relevant within the benefits analysis.

ie_year is set to 2017 to deactivate within model runs.;

DATA reductions_detail;

SET reductions_detail;

if region="" then delete;

RUN;

PROC SORT data=reductions_detail;

By option_name region;

RUN;

Data i_only_1;

set i_only;

DO imp_benefit_value_regional=benefit_value_regional;

END;

IF species_econ~="zz.all.species" then delete;

IF metric~="ae1" then delete;

if comrec_type~="all.types" then delete;

IF region="zz.allregions" then delete;

DROP lossmode;

RUN;

PROC SORT data=i_only_1;

By option_name region value_basis species_econ metric comrec_type;

RUN;

Data ent_only_1;

set ent_only;

DO ent_benefit_value_regional=benefit_value_regional;

END;

IF species_econ~="zz.all.species" then delete;

IF metric~="ae1" then delete;

if comrec_type~="all.types" then delete;

IF region="zz.allregions" then delete;

DROP lossmode;

RUN;

PROC SORT data=ent_only_1;

by option_name region value_basis species_econ metric comrec_type;

RUN;

Data ie_rd1;

set ie;

drop f10 f11;

DO ie_benefit_value_regional=benefit_value_regional;

END;

IF species_econ~="zz.all.species" then delete;

IF metric~="ae1" then delete;

if comrec_type~="all.types" then delete;

IF region="zz.allregions" then delete;

if region="zz.allregions" then delete;

RUN;

PROC SORT data=ie_rd1;

by option_name region value_basis species_econ metric comrec_type;

RUN;

DATA ie_rd2;

Page 2
merge ie_rd1 i_only_1 ent_only_1;

by option_name region value_basis species_econ metric comrec_type;

RUN;

Data ie_rd3;

merge ie_rd2 reductions_detail;

by option_name region;

IF ent_benefit_value_regional="." then do ent_benefit_value_regional=0;

END;

IF imp_benefit_value_regional="." then do imp_benefit_value_regional=0;

END;

imp_frac_imp=(1-ccr_fraction_imp);

IF ie_benefit_value_regional~=0 then do

impingement_fraction=((imp_frac_imp*imp_benefit_value_regional)/ie_benefit_value_

regional);

END;

IF ie_benefit_value_regional=0 then do impingement_fraction="";

END;

DROP ie_benefit_value_regional imp_benefit_value_regional

ent_benefit_value_regional ccr_fraction_imp imp_frac_imp

value_basis species_econ lossmode metric comrec_type

baseline_value_regional benefit_value_regional;

RUN;

Proc Sort data=ie out=ie;

by option_name region;

RUN;

PROC Sort data=ie_rd3 out=ie_rd3;

by option_name region;

RUN;

Data ie;

merge ie ie_rd3;

by option_name region;

drop f10 f11;

RUN;

*ADJUST BASELINE FOR CONSISTENCY WITH SURVEY MATERIALS*****************

The survey materials did not account for state regulations of cooling

water intake structures in California and New York. Import baseline IM&E

losses that reflect "EXISTING" conditions rather than IM&E given compliance

with California and New York state regulations;

%import(&facloc.&input3,BNB_IE,base_in);

Data base_in;

set base_in;

rename baseline_value_regional=full_base;

drop benefit_value_regional option_name;

RUN;

proc sql;

create table ie_x as select

a.*,

b.*

from ie as a left join base_in as b

on a.region=b.region

and a.value_basis=b.value_basis and a.species_econ=b.species_econ

and a.lossmode=b.lossmode and a.metric=b.metric

and a.comrec_type=b.comrec_type;

quit;

*COMPILE DATA FOR %CHANGES in A1E by Stated Preference Survey Region***;

*import facility data;

%import(&facloc.&fac_input,facility_data,facility_data);

*import survey region crosswalk and household data;

%import(&inloc.&input,state_list,state_list);

*import facility level data for flow;

%import(&facloc.&fac_input,flowdata,flowdata);

*import the breakdown of regional AIF by state;

%import(&inloc.&input,state_AIF_frac,state_AIF_frac);

*allow for the specification of alternative values for California and

New York facilities within the scope of state regulations of

cooling water intake structures;

Page 3
%let CA_all_fac_baseline="no";

%let NY_all_fac_baseline="no";

data ie_a1e;

set ie_x;

if metric~="ae1" then delete;

if species_econ~="zz.all.species" then delete;

if comrec_type~="all.types" then delete;

if region="zz.allregions" then delete;

drop value_basis species_econ lossmode metric comrec_type;

RUN;

Data state_aif_frac;

format region $20.;

set state_aif_frac;

if state="total" then delete;

if region="GL" then do region="greatlakes"; end;

if region="INL" then do region="inland"; end;

if region="Mex" then do region="gulfmexico"; end;

if region="SA" then do region="southatlantic"; end;

if region="NA" then do region="northatlantic"; end;

if region="CAL" then do region="calif"; end;

if region="MA" then do region="midatlantic"; end;

RUN;

proc sql;create table ie_a1e_1 as select a.*,b.*

from ie_a1e as a left join state_aif_frac as b on a.region=b.region; quit;

Data ie_a1e_1; set ie_a1e_1;

ben_val_reg_adj=benefit_value_regional*frac_AIF;

bas_val_reg_adj=baseline_value_regional*frac_AIF;

full_base_adj=full_base*frac_AIF;

RUN;

proc sql; create table ie_a1e_2 as select a.*,b.*

from ie_a1e_1 as a left join state_list as b on a.state=b.state; quit;

proc sql; create table ie_a1e_3 as select

option_name,

SP_Survey_Region,

sum(ben_val_reg_adj) as ben_val_SPreg_adj,

sum(bas_val_reg_adj) as bas_val_SPreg_adj,

sum(full_base_adj) as full_base_adj

from ie_a1e_2 group by option_name, sp_survey_region; quit;

*import the regional totals for each household;

%import(&inloc.&input,HH_survey_reg,HH_survey_reg);

proc sql; create table ie_a1e_3_nat as select

option_name,

SP_survey_region,

sum(ben_val_SPreg_adj) as ben_val_SPreg_adj,

sum(bas_val_SPreg_adj) as bas_val_SPreg_adj,

sum(full_base_adj) as full_base_adj

from ie_a1e_3 group by option_name; quit;

Data ie_a1e_3_nat; set ie_a1e_3_nat; SP_Survey_Region="National";RUN;

proc sort data=ie_a1e_3_nat out=ie_a1e_3_nat nodupkey; by option_name

SP_survey_region; RUN;

Data ie_a1e_4;

set ie_a1e_3 ie_a1e_3_nat;

peak_per_fish_sav=(1-(full_base_adj-ben_val_SPreg_adj)/full_base_adj)*100;

RUN;

Data ie_a1e_4_basex;

set ie_a1e_4;

keep SP_survey_region bas_val_SPreg_adj full_base_adj;

RUN;

proc sort data=ie_a1e_4_basex out=ie_a1e_basex1 nodupkey;

by SP_survey_region;

RUN;

Data ie_a1e_basex1;

set ie_a1e_basex1;

option_name="baseline";

peak_per_fish_sav=(bas_val_SPreg_adj/full_base_adj)*100;

drop bas_val_SPreg_adj full_base_adj;

RUN;

Page 4
Data ie_a1e_4x;

set ie_a1e_4;

drop bas_val_SPreg_adj ben_val_SPreg_adj full_base_adj;

RUN;

Data ie_a1e_4x;

set ie_a1e_4x ie_a1e_basex1;

RUN;

proc sql; create table ie_a1e_5 as select a.*,b.* from ie_a1e_4x as a left join

HH_survey_reg as b on a.sp_survey_region=b.sp_survey_region; quit;

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

*import the implicit prices estimated by the surveys;

%import(&inloc.&input,implicit_prices,implicit_prices);

*import the changes under regulatory options;

%import(&inloc.&input,rule_changes,rule_changes);

proc sql; create table CS as select

a.*,b.* from ie_a1e_5 as a left join rule_changes as b on

a.option_name=b.option_name and a.SP_survey_region=b.SP_survey_region;

quit;

*macro to calculate separate values for low, mean, and high implicit prices;

%macro CSCI(val);

Data implicit_prices_&val.; set implicit_prices;

if Value~="&val." then delete;

*specific thresholds for flattening of the WTP function - potentially

relevant for quadratic models;

Keep model SP_survey_region Value IP_neither IP_com_fish IP_fish_pop IP_fish_sav

IP_fish_sav_sq IP_aquatic fish_sav_WTP_threshold fish_sav_sig fish_sav_sq_sig;

RUN;

proc sql; create table CS_&val. as select

a.*,b.* from CS as a left join implicit_prices_&val. as b on

a.SP_survey_region=b.SP_survey_region;

quit;

Data CS_&val.; set CS_&val.;

IF option_name="baseline" then do option="baseline"; end;

IF option_name="option.tmn1" then do option="tmn1"; end;

IF option_name="option.tmn2" then do option="tmn2"; end;

IF option_name="option.tmn3" then do option="tmn3"; end;

drop option_name;

RUN;

%mend;

%CSCI(mean);%CSCI(low);%CSCI(high);

*create file to weight the compliance schedule for each SP survey region based on

IM&E data

for 316b regions;

Data SP_comp_weight; set IE_a1e_2;

keep option_name region SP_survey_region ben_val_reg_adj bas_val_reg_adj;

RUN;

proc sql; create table SP_comp_weight_1 as select

option_name, region, sp_survey_region,

sum(ben_val_reg_adj) as ben_val_reg_adj,

sum(bas_val_reg_adj) as bas_val_reg_adj

from SP_comp_weight group by option_name,region,sp_survey_region; quit;

proc sql; create table SP_comp_weight_2 as select

option_name,region,SP_survey_region,ben_val_reg_adj,bas_val_reg_adj,

sum(ben_val_reg_adj) as ben_val_SPreg_tot,

sum(bas_val_reg_adj) as bas_val_SPreg_tot

from sp_comp_weight_1 as a group by SP_survey_region, option_name; quit;

*reformat the data set to stack baseline and options;

Data SP_comp_weight_2_bas; set SP_comp_weight_2; drop ben_val_reg_adj

ben_val_SPreg_tot;

rename bas_val_reg_adj=ben_val_reg_adj bas_val_SPreg_tot=ben_val_SPreg_tot;

option_name="baseline"; RUN;

proc sort data=SP_comp_weight_2_bas out=SP_comp_weight_2_bas nodupkey; by region

sp_survey_region; RUN;

Page 5
Data SP_comp_weight_3; set SP_comp_weight_2 SP_comp_weight_2_bas;

drop bas_val_reg_adj bas_val_SPreg_tot;

if region="calif" then calif_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

if region="greatlakes" then

greatlakes_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

if region="inland" then inland_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

if region="midatlantic" then

midatlantic_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

if region="southatlantic" then

southatlantic_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

if region="northatlantic" then

northatlantic_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

if region="gulfmexico" then

gulfmexico_comp_weight=ben_val_reg_adj/ben_val_SPreg_tot;

IF option_name="baseline" then option="baseline";

IF option_name="option.tmn1" then option="tmn1";

IF option_name="option.tmn2" then option="tmn2";

IF option_name="option.tmn3" then option="tmn3";

RUN;

proc sql; create table SP_comp_weight_4 as select

option, SP_survey_region,

sum(calif_comp_weight) as calif_comp_weight,

sum(greatlakes_comp_weight) as greatlakes_comp_weight,

sum(inland_comp_weight) as inland_comp_weight,

sum(midatlantic_comp_weight) as midatlantic_comp_weight,

sum(southatlantic_comp_weight) as southatlantic_comp_weight,

sum(northatlantic_comp_weight) as northatlantic_comp_weight,

sum(gulfmexico_comp_weight) as gulfmexico_comp_weight

from SP_comp_weight_3 group by option, SP_Survey_Region;

quit;

*edit region names;

DATA facility_data;

Set facility_data;

IF Region="CN" or Region ="CS" or Region ="HI" then reg="CAL";

IF Region="INL" then reg="INL";

IF Region="GL" then reg="GL";

IF Region="MA" then reg="MA";

IF Region="NA" then reg="NA";

IF Region="SA" then reg="SA";

IF Region="Mex" then reg="Mex";

IF Region="VI" then reg="VI";

IF Region="PNW" then reg="PNW";

RUN;

*ALL REMAINING STEPS ARE RELATED TO DISCOUNTING/COMPLIANCE SCHEDULE*********;

*The purpose of the following two data steps is to manipulate the data so the

compliance schedule can be properly applied;

data ie;

set ie;

if metric~="ae1" then delete;

if comrec_type~="all.types" then delete;

if region="zz.allregions" then delete;

RUN;

Proc sql;

create table ie_2 as

select option_name,

region,

species_econ,

lossmode,

metric,

comrec_type,

baseline_value_regional,

benefit_value_regional,

impingement_fraction,

sum(baseline_value_regional) as baseline_sum,

sum(benefit_value_regional) as benefit_sum

From ie group by option_name,species_econ;

Page 6
Quit;

Data ie_3;

Set ie_2;

IF option_name="option.tmn1" then option="tmn1";

IF option_name="option.tmn2" then option="tmn2";

IF option_name="option.tmn3" then option="tmn3";

DROP option_name;

reg=region;

DROP region;

RUN;

*Import facility data with both facility id's;

%import(&facloc.&fac_input,IDMAP,f1);

*Import data with weights;

%import(&facloc.&fac_input,ID_AND_WEIGHT,ID_AND_WEIGHT);

*Import data with region and flow;

%import(&facloc.&fac_input,flowdata,f3);

*Import data with compliance dates;

%import(&facloc.&fac_input,complianceyear,cydata);

%macro cyr;

cydata.tmn1 as cyr_base,

cydata.tmn1 as cyr_tmn1,

cydata.tmn2 as cyr_tmn2,

cydata.tmn3 as cyr_tmn3

%mend;

*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 right join ID_AND_WEIGHT as w on id.surveyid=w.id;

quit;

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

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

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

Page 7
else if Benefits_Region="SA"

then region="South Atlantic";

else if Benefits_Region="VI"

then delete;

else if Benefits_Region="PNW"

then delete;

run;

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

RUN;

DATA s2a;

SET s2a;

if cyr_base="." then delete;

if PLNTCODE="." then delete;

RUN;

*This macro calculates benefits schedule for facilities installing

impingement tech only as specified based on the ie_year variable.

Note: this distinction is no longer relevant. Ie_year is set to 2017

to deactive it within the code;

*assumes no biological lag;

%macro step5schedule(opt,cyr,opt_short);

*generate benefits schedule;

data s3; *s3 contains plntcodes weights and compliance years along with the flow

arrays (flow arrays are equal to AIF)

and includes the biological fraction as well. It multiplies these to get the

biological schedule;

set s2a;

where region~="Virgin Islands";

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;

if region="Inland" |

region="Great Lakes" | region="Gulf of Mexico" then do; *benefits begin in first

year for these regions;

*this assumes no

biological lag;

if i=1 then bio(i)=1;

if i=2 then bio(i)=1;

if i=3 then bio(i)=1;

if i=4 then bio(i)=1;

if i=5 then bio(i)=1;

if i=6 then bio(i)=1;

if i>6 and i<=46 then

bio(i)=1;

if i=47 then bio(i)=0;

if i=48 then bio(i)=0;

if i=49 then bio(i)=0;

if i=50 then bio(i)=0;

if i=51 then bio(i)=0;

if i>51 then bio(i)=0;

end;

Page 8
else do; *benefits also begin in

the first year for these regions;

if i=1 then bio(i)=1;

if i=2 then bio(i)=1;

if i=3 then bio(i)=1;

if i=4 then bio(i)=1;

if i=5 then bio(i)=1;

if i=6 then bio(i)=1;

if i=7 then bio(i)=1;

if i>7 and i<=46 then

bio(i)=1;

if i=47 then bio(i)=0;

if i=48 then bio(i)=0;

if i=49 then bio(i)=0;

if i=50 then bio(i)=0;

if i=51 then bio(i)=0;

if i>51 then bio(i)=0;

end;

end;

else do;

if &cyr.>=&ie_year. then

delete;

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 = 2011 and first year of rule is 2011 then x=1 in 2011;

if

x<=0 then flowy_(i)=0; *x=1 in first year of compliance;

else

flowy_(i)=avgflow*&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)=1;

else if x=3

then bio(i)=1;

else if x=4

then bio(i)=1;

else if x=5

then bio(i)=1;

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

else if i=48

then bio(i)=0;

else if i=49

then bio(i)=0;

else if i=50

then bio(i)=0;

else if i=51

then bio(i)=0;

else if i>51

Page 9
then bio(i)=0;

end;

else do;

*benefits also

begin in first year for these regions;

x=i-&cyr.+&t0;

*e.g., if i=1 and cyr = 2011 and first year of rule is 2011 then x=1 in 2012;

if

x<0 then flowy_(i)=0;*x=1 in second year of compliance;

else

flowy_(i)=avgflow*&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)=1;

else if x=3

then bio(i)=1;

else if x=4

then bio(i)=1;

else if x=5

then bio(i)=1;

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=10

then bio(i)=1;

else if x>=10

and i<=46 then bio(i)=1;

else if i=47

then bio(i)=0;

else if i=48

then bio(i)=0;

else if i=49

then bio(i)=0;

else if i=50

then bio(i)=0;

else if i=51

then bio(i)=0;

else if i>51

then bio(i)=0;

end;

end;

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

end;

format reg $30.;

if region="California"

then reg="calif";

else if region="Great Lakes"

then reg="greatlakes";

else if region="Gulf of Mexico"

then reg="gulfmexico";

else if region="Inland"

then reg="inland";

else if region="Mid-Atlantic"

then reg="midatlantic";

else if region="North Atlantic"

then reg="northatlantic";

else if region="South Atlantic"

Page 10
then reg="southatlantic";

run;

*calculate total average flow by region;

proc sql;

create table s5 as select

s3.*,

sum(avgflow*newweight*&opt_short._newtech) as totalavgflow

/*group flag*/

from s3 group by region;

quit;

*generate flow and benefits schedule variables (for each facility) as percentages

to total regional average flow;

data s6 (drop=bio_1-bio_60 flowy_1-flowy_60);

set s5;

array flowy_(60) flowy_1-flowy_60;

array bschd(60) bschd_1-bschd_60;

do i=1 to 60;

IF totalavgflow~=0 then

flowy_(i)=newweight*flowy_(i)/totalavgflow;

IF totalavgflow=0 then flowy_(i)=0;

IF totalavgflow~=0 then

bschd(i)=newweight*bschd(i)/totalavgflow;

IF totalavgflow=0 then bschd(i)=0;

end;

run;

*sort data by region;

proc sort data=s6 out=s7; by region; run;

*generate benefits schedule variable for region, e.g., 1 = full benefits, .5 =

50% of full benefits;

data s8_&opt (keep=option region reg rbschd_1-rbschd_60);

retain option region reg rbschd_1-rbschd_60;

set ;

by region;

option="&opt";

array bschd(60) bschd_1-bschd_60;

array rbschd(60) rbschd_1-rbschd_60;

do i=1 to 60;

if first.region then rbschd(i)=bschd(i);

else rbschd(i)+bschd(i);

end;

if last.region;

run;

%mend;

*This macro calculates benefits schedule for facilities installing

entrainment tech as specified based on the ie_year variable.

Note: this distinction is no longer relevant. Ie_year is set to 2017

to deactive it within the code;

*assumes no biological lag;

%macro step6schedule(opt,cyr,opt_short);

data s4; *s3 contains plntcodes weights and compliance years along with the flow

arrays (flow arrays are equal to AIF)

and includes the biological fraction as well. It multiplies these to get the

biological schedule;

set s2a;

where region~="Virgin Islands";

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;

if region="Inland" | region="Great Lakes"

| region="Gulf of Mexico" then do;

Page 11
if i=1 then bio(i)=1;

if i=2 then bio(i)=1;

if i=3 then bio(i)=1;

if i=4 then bio(i)=1;

if i=5 then bio(i)=1;

if i=6 then bio(i)=1;

if i>6 and i<=46 then

bio(i)=1;

if i=47 then bio(i)=0;

if i=48 then bio(i)=0;

if i=49 then bio(i)=0;

if i=50 then bio(i)=0;

if i=51 then bio(i)=0;

if i>51 then

bio(i)=0;*benefits begin in first year for these regions;

end;

else do;

if i=1 then bio(i)=1;

if i=2 then bio(i)=1;

if i=3 then bio(i)=1;

if i=4 then bio(i)=1;

if i=5 then bio(i)=1;

if i=6 then bio(i)=1;

if i=7 then bio(i)=1;

if i>7 and i<=46 then

bio(i)=1;

if i=47 then bio(i)=0;

if i=48 then bio(i)=0;

if i=49 then bio(i)=0;

if i=50 then bio(i)=0;

if i=51 then bio(i)=0;

if i>51 then

bio(i)=0;*benefits also begin in the first for these regions;

end;

end;

else do;

if &cyr.<&ie_year. then

delete;

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 = 2011 and first year of rule is 2011 then x=1 in 2011;

if

x<=0 then flowy_(i)=0; *x=1 in first year of compliance;

else

flowy_(i)=avgflow*&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)=1;

else if x=3

then bio(i)=1;

else if x=4

then bio(i)=1;

else if x=5

then bio(i)=1;

else if x=6

then bio(i)=1;

else if x=7

then bio(i)=1;

else if x=8

Page 12
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)=0;

else if i=48

then bio(i)=0;

else if i=49

then bio(i)=0;

else if i=50

then bio(i)=0;

else if i=51

then bio(i)=0;

else if i>51

then bio(i)=0;

end;

else do;

*benefits also

begin in first year for these regions;

x=i-&cyr.+&t0;

*e.g., if i=1 and cyr = 2011 and first year of rule is 2011 then x=1 in 2012;

if

x<0 then flowy_(i)=0;*x=1 in second year of compliance;

else

flowy_(i)=avgflow*&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)=1;

else if x=3

then bio(i)=1;

else if x=4

then bio(i)=1;

else if x=5

then bio(i)=1;

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=10

then bio(i)=1;

else if x>=10

and i<=46 then bio(i)=1;

else if i=47

then bio(i)=0;

else if i=48

then bio(i)=0;

else if i=49

then bio(i)=0;

else if i=50

then bio(i)=0;

else if i=51

then bio(i)=0;

else if i>51

then bio(i)=0;

end;

end;

Page 13
bschd(i)=(flowy_(i)*bio(i));

end;

format reg $30.;

if region="California"

then reg="calif";

else if region="Great Lakes"

then reg="greatlakes";

else if region="Gulf of Mexico"

then reg="gulfmexico";

else if region="Inland"

then reg="inland";

else if region="Mid-Atlantic"

then reg="midatlantic";

else if region="North Atlantic"

then reg="northatlantic";

else if region="South Atlantic"

then reg="southatlantic";

run;

*calculate total average flow by region;

proc sql;

create table s5 as select

s4.*,

sum(avgflow*newweight*&opt_short._newtech) as totalavgflow

/*group flag*/

from s4 group by region;

quit;

*generate flow and benefits schedule variables (for each facility) as percentages

to total regional average flow;

data s6 (drop=bio_1-bio_60 flowy_1-flowy_60);

set s5;

array flowy_(60) flowy_1-flowy_60;

array bschd(60) bschd_1-bschd_60;

do i=1 to 60;

IF totalavgflow~=0 then

flowy_(i)=newweight*flowy_(i)/totalavgflow;

IF totalavgflow=0 then flowy_(i)=0;

IF totalavgflow~=0 then

bschd(i)=newweight*bschd(i)/totalavgflow;

IF totalavgflow=0 then bschd(i)=0;

end;

run;

*sort data by region;

proc sort data=s6 out=s25; by region; run;

*generate benefits schedule variable for region, e.g., 1 = full benefits, .5 =

50% of full benefits;

data s9_&opt (keep=option region reg rbschd_1-rbschd_60);

retain option region reg rbschd_1-rbschd_60;

set s25;

by region;

option="&opt";

array bschd(60) bschd_1-bschd_60;

array rbschd(60) rbschd_1-rbschd_60;

do i=1 to 60;

if first.region then rbschd(i)=bschd(i);

else rbschd(i)+bschd(i);

end;

if last.region;

run;

%mend;

*This macro calculates benefits schedule, incorporating the biological lag and

the lag in compliance;

*Generate dataset of Benefits Schedule By Option;

%macro optionmac00;

%step5schedule(baseline,cyr_base,base);

%step5schedule(tmn1,cyr_tmn1,tmn1);

Page 14
%step5schedule(tmn2,cyr_tmn2,tmn2);

%step5schedule(tmn3,cyr_tmn3,tmn3);

%mend;

%macro optionmac11;

%step6schedule(baseline,cyr_base,base);

%step6schedule(tmn1,cyr_tmn1,tmn1);

%step6schedule(tmn2,cyr_tmn2,tmn2);

%step6schedule(tmn3,cyr_tmn3,tmn3);

%mend;

%optionmac00;

%optionmac11;

*step5data contains the benefits schedules for all options for impingement tech

as noted above, this distiction is no longer relevant in the analysis;

data Step5data;

length option $50.0;

set s8_baseline s8_tmn1 s8_tmn2 s8_tmn3;

IF region="" THEN DELETE;

DROP region;

array rbschd_(60) rbschd_1-rbschd_60;

do i=1 to 60;

IF option="" then rbschd_(i)=0;

END;

run;

proc sort data=step5data out=step5data_1;

by reg;

RUN;

*step20data contains the benefits schedules for all options for entrainment tech

as noted above, this distiction is no longer relevant in the analysis;

data Step20data;

length option $50.0;

set s9_baseline s9_tmn1 s9_tmn2 s9_tmn3;

IF region="" THEN DELETE;

DROP region;

array rbschd_(60) rbschd_1-rbschd_60;

array sbschd_(60) sbschd_1-sbschd_60;

Do i=1 to 60;

sbschd_(i)=rbschd_(i);

DROP rbschd_1-rbschd_60;

END;

run;

proc sort data=step20data out=step20data_1;

by reg;

RUN;

*comp_calcs contains the seperate benefit schedules for all tech types;

data comp_calcs;

merge step5data_1 step20data_1;

by reg option;

run;

Proc sort data=comp_calcs out=comp_calcs;

by reg option;

Run;

*ie_imp brings in the proportion of IM&E attributable to impingement only tech

(i.e., the "impingement fraction"). as noted above, the "impingement fraction"

is no longer relevant in the analysis;

Data ie_imp;

LENGTH option $20;

SET ie (KEEP = option_name region metric comrec_type species_econ

impingement_fraction);

If option_name="option.tmn1" then option="tmn1";

else if option_name="option.tmn2" then option="tmn2";

else if option_name="option.tmn3" then option="tmn3";

if region="zz.allregions" then delete;

if metric~="ae1" then delete;

if comrec_type~="all.types" then delete;

drop option_name;

reg=region;

drop region;

drop species_econ;

Page 15
drop metric;

drop comrec_type;

RUN;

Proc sort data=ie_imp out=ie_imp;

by reg option;

Run;

data comp_imp;

merge ie_imp comp_calcs;

by reg option;

if option="baseline" then impingement_fraction=0;

run;

*comp_calcs_1 calculates the final benefit schedule;

data comp_calcs_1;

set comp_imp;

array rbschd_(60) rbschd_1-rbschd_60;

array sbschd_(60) sbschd_1-sbschd_60;

array fschd_(60) fschd_1-fschd_60;

Do i=1 to 60;

IF rbschd_(i)="." then rbschd_(i)=0;

IF sbschd_(i)="." then sbschd_(i)=0;

fschd_(i)=impingement_fraction*rbschd_(i)+(1-impingement_fraction)*sbschd_(i);

max_fschd=round(max(of fschd_1-fschd_60),.000000000000001);*included to

account for rounding error.

*max(of fschd_1-fschd_60) ~= 1 then put _n_ "error flag 1 - fschd does

not sum to 1";

End;

Run;

*data checks;

data comp_calcs_1;

set comp_calcs_1;

if max_fschd >1 then put _n_ "error flag 1 - fschd is greater than 1 "

max_fschd;

if max_fschd <1 then put _n_ "error flag 2 - fschd is less than 1 "

max_fschd;

RUN;

proc sort data=comp_calcs_1 out=comp_calcs_1 nodupkey; by option reg; RUN;

*comp_calcs_2 contains the final compliance schedule for each option with the

final biological schedules listed vertically;

DATA comp_calcs_2;

SET comp_calcs_1;

Drop rbschd_1-rbschd_60;

Drop sbschd_1-sbschd_60;

Drop impingement_fraction;

Drop i max_fschd;

RUN;

*REPOWERING CALCULATIONS****************************************************;

*Note: repowerings are not included in the final rule and other options

considered for existing units. Code related to repowerings is not relevant

for the analysis for the rule;

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

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

Proc sort data=rep_adj out=rep_adj;

by option;

RUN;

Proc sort data=rpschd out=rpschd;

by option;

RUN;

Data repow;

merge rep_adj rpschd;

by option;

adj=1/rp_adj;

drop rp_adj;

RUN;

Page 16
Data repow_1;

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 rp_adj rpschd_1-rpschd_60 i;

if lossmode="imp" then delete;

if lossmode="ent" then delete;

if region="zz.allregions" then delete;

if option= "baseline_0" | option="tmn1_0" | option="tmn2_0" |

option="tmn3_0" then delete;

reg=region;

drop region lossmode; *all values only refer to IM&E combined;

RUN;

Proc Sort data=repow_1 out=repow_1;

by option reg;

RUN;

Proc Sort data=comp_calcs_2 out=comp_calcs_2;

by option reg;

RUN;

Data comp_calcs_2;

merge repow_1 comp_calcs_2;

by option reg;

RUN;

Data comp_calcs_2;

set comp_calcs_2;

array fschd_(60) fschd_1-fschd_60;

array rpschd_adj_(60) rpschd_adj_1-rpschd_adj_60;

array fschd_adj_(60) fschd_adj_1-fschd_adj_60;

do i = 1 to 60;

fschd_adj_(i)=fschd_(i)*adj + rpschd_adj_(i);

fschd_(i) = fschd_adj_(i);

END;

drop i rpschd_adj_1-rpschd_adj_60 fschd_adj_1-fschd_adj_60 adj;

RUN;

*END OF REPOWERING

CALCULATIONS****************************************************;

*IMPLEMENT DISCOUNTING AND ANNUALIZATION

CALCS*************************************;

%macro red_adj(reg);

Data comp_calcs_3_®.; set comp_calcs_2;

array fschd_(60) fschd_1-fschd_60;

array ®._(60) ®._1-®._60;

do i = 1 to 60;

if reg="®." then ®._(i)=fschd_(i);

end; drop i fschd_1-fschd_60;

if reg~="®." then delete; drop reg;

run;

%mend;

%red_adj(calif);%red_adj(greatlakes);%red_adj(inland);%red_adj(midatlantic);

%red_adj(southatlantic);%red_adj(northatlantic);%red_adj(gulfmexico);

proc sort data=Sp_comp_weight_4 out=Sp_comp_weight_4; by option;RUN;

Data comp_calcs_5;

merge Sp_comp_weight_4 Comp_calcs_3_calif Comp_calcs_3_greatlakes

Comp_calcs_3_inland

Comp_calcs_3_midatlantic Comp_calcs_3_southatlantic

Comp_calcs_3_northatlantic

Comp_calcs_3_gulfmexico;

Page 17
by option;

RUN;

Data comp_calcs_6; set comp_calcs_5;

array schd_(60) schd_1-schd_60;

array calif_(60) calif_1-calif_60;

array greatlakes_(60) greatlakes_1-greatlakes_60;

array inland_(60) inland_1-inland_60;

array midatlantic_(60) midatlantic_1-midatlantic_60;

array southatlantic_(60) southatlantic_1-southatlantic_60;

array northatlantic_(60) northatlantic_1-northatlantic_60;

array gulfmexico_(60) gulfmexico_1-gulfmexico_60;

if calif_comp_weight="." then calif_comp_weight=0;

if greatlakes_comp_weight="." then greatlakes_comp_weight=0;

if inland_comp_weight="." then inland_comp_weight=0;

if midatlantic_comp_weight="." then midatlantic_comp_weight=0;

if southatlantic_comp_weight="." then southatlantic_comp_weight=0;

if northatlantic_comp_weight="." then northatlantic_comp_weight=0;

if gulfmexico_comp_weight="." then gulfmexico_comp_weight=0;

do i = 1 to 60;

schd_(i)=(calif_(i)*calif_comp_weight+

greatlakes_(i)*greatlakes_comp_weight+

inland_(i)*inland_comp_weight+

midatlantic_(i)*midatlantic_comp_weight+

southatlantic_(i)*southatlantic_comp_weight+

northatlantic_(i)*northatlantic_comp_weight+

gulfmexico_(i)*gulfmexico_comp_weight);

end;keep option sp_survey_region schd_1-schd_60;

RUN;

*additional step to add in the compliance schedule for the national SP region;

Data ie_a1e_4_nat; set ie_a1e_4;

drop per_fish_sav;

if option_name="baseline" ten option="baseline";

IF option_name="option.tmn1" then option="tmn1";

IF option_name="option.tmn2" then option="tmn2";

IF option_name="option.tmn3" then option="tmn3";

drop option_name;

RUN;

proc sort data=ie_a1e_4_nat out=ie_a1e_4_nat;

by SP_survey_region option;

RUN;

proc sort data=comp_calcs_6 out=comp_calcs_7;

by SP_survey_region option;;

RUN;

Data comp_calcs_7;

merge ie_a1e_4_nat comp_calcs_7;

by SP_survey_region option;

RUN;

Data ie_a1e_4_natxb; set ie_a1e_4_nat;

drop ben_val_SPreg_adj;

option="baseline";

if SP_survey_region~="National" then delete;

RUN;

proc sort data=ie_a1e_4_natxb out=ie_a1e_4_natxb nodupkey;

by SP_survey_region;

RUN;

Data comp_calcs_7;

set comp_calcs_7 ie_a1e_4_natxb;

RUN;

proc sort data=comp_calcs_7 out=comp_calcs_7;

by sp_survey_region option;

RUN;

Data ie_a1e_4_natx; set ie_a1e_4_nat;

drop ben_val_SPreg_adj;

option="baseline";

rename bas_val_SPreg_adj=base;

RUN;

proc sort data=ie_a1e_4_natx out=ie_a1e_4_natx nodupkey;

Page 18
by SP_survey_region;

RUN;

proc sort data=comp_calcs_7 out=comp_calcs_7;

by SP_survey_region;

RUN;

Data comp_calcs_7;

merge comp_calcs_7 ie_a1e_4_natx;

by SP_survey_region;

bas_val_SPreg_adj=base; drop base;

if option="baseline" then do ben_val_SPreg_adj=bas_val_SPreg_adj; end;

if option="baseline" then peak_per_fish_sav=100;

RUN;

data comp_calcs_7reg;

set comp_calcs_7; if sp_survey_region="National" then delete;

RUn;

data comp_calcs_7nat;

set comp_Calcs_7; if sp_survey_region~="National" then delete;

rename ben_val_SPreg_adj=ben_nat bas_val_SPreg_adj=bas_nat;

drop schd_1-schd_60 sp_survey_region;

RUN;

proc sort data=comp_calcs_7reg out=comp_calcs_7reg;

by option;

RUN;

proc sort data=comp_calcs_7nat out=comp_calcs_7nat;

by option;

RUN;

Data comp_calcs_8nat;

merge comp_calcs_7reg comp_calcs_7nat;

by option;

drop bas_val_SPreg_adj bas_nat;

array schd_(60) schd_1-schd_60;

array adj_(60) adj_1-adj_60;

do i=1 to 60;

adj_(i)=(ben_val_SPreg_adj/ben_nat)*schd_(i);

end; drop i schd_1-schd_60 ben_val_SPreg_adj ben_nat peak_per_fish_sav;

RUN;

proc sql;

create table comp_calcs_8nat_1 as select

a.*,

sum(adj_1) as nat_1,

sum(adj_2) as nat_2,

sum(adj_3) as nat_3,

sum(adj_4) as nat_4,

sum(adj_5) as nat_5,

sum(adj_6) as nat_6,

sum(adj_7) as nat_7,

sum(adj_8) as nat_8,

sum(adj_9) as nat_9,

sum(adj_10) as nat_10,

sum(adj_11) as nat_11,

sum(adj_12) as nat_12,

sum(adj_13) as nat_13,

sum(adj_14) as nat_14,

sum(adj_15) as nat_15,

sum(adj_16) as nat_16,

sum(adj_17) as nat_17,

sum(adj_18) as nat_18,

sum(adj_19) as nat_19,

sum(adj_20) as nat_20,

sum(adj_21) as nat_21,

sum(adj_22) as nat_22,

sum(adj_23) as nat_23,

sum(adj_24) as nat_24,

sum(adj_25) as nat_25,

sum(adj_26) as nat_26,

sum(adj_27) as nat_27,

sum(adj_28) as nat_28,

sum(adj_29) as nat_29,

sum(adj_30) as nat_30,

Page 19
sum(adj_31) as nat_31,

sum(adj_32) as nat_32,

sum(adj_33) as nat_33,

sum(adj_34) as nat_34,

sum(adj_35) as nat_35,

sum(adj_36) as nat_36,

sum(adj_37) as nat_37,

sum(adj_38) as nat_38,

sum(adj_39) as nat_39,

sum(adj_40) as nat_40,

sum(adj_41) as nat_41,

sum(adj_42) as nat_42,

sum(adj_43) as nat_43,

sum(adj_44) as nat_44,

sum(adj_45) as nat_45,

sum(adj_46) as nat_46,

sum(adj_47) as nat_47,

sum(adj_48) as nat_48,

sum(adj_49) as nat_49,

sum(adj_50) as nat_50,

sum(adj_51) as nat_51,

sum(adj_52) as nat_52,

sum(adj_53) as nat_53,

sum(adj_54) as nat_54,

sum(adj_55) as nat_55,

sum(adj_56) as nat_56,

sum(adj_57) as nat_57,

sum(adj_58) as nat_58,

sum(adj_59) as nat_59,

sum(adj_60) as nat_60

from comp_calcs_8nat as a group by option;

quit;

Data comp_calcs_8nat_1;

set comp_calcs_8nat_1;

drop adj_1-adj_60;

SP_survey_region="National";

rename nat_1-nat_60=schd_1-schd_60;

RUN;

proc sort data=comp_calcs_8nat_1 out=comp_calcs_8nat_1 nodupkey;

by sp_survey_region option;

RUN;

Data comp_calcs_6;

set comp_calcs_6 comp_calcs_8nat_1;

RUN;

*CALCULATE WTP PER HOUSEHOLD FOR EACH YEAR OF THE ANALYSIS

PERIOD******************;

%macro CS_per_HH(val);

proc sql; create table CS_per_HH_&val. as select

a.*,

b.*

from CS_&val. as a, comp_calcs_6 as b where a.option=b.option and

a.SP_survey_region=b.SP_survey_region;

quit;

*multiply the the peak fish saved by the compliance schedule;

*drop other attributes from the data file (aquatic ecosystem condition, fish pop,

and com fish pop) because

benefits are being estimated based on fish saved;

Data CS_per_HH_&val.; set CS_per_HH_&val.;

drop per_com_fish per_fish_pop per_aquatic IP_neither IP_com_fish IP_fish_pop

IP_aquatic;

array schd_(60) schd_1-schd_60;

array per_fish_sav_(60) per_fish_sav_1-per_fish_sav_60;

array per_fish_sq_(60) per_fish_sq_1-per_fish_sq_60;

array CS_per_HH_&val._(60) CS_per_HH_&val._1-CS_per_HH_&val._60;

array CS_reg_&val._(60) CS_reg_&val._1-CS_reg_&val._60;

peak_fish_sq=(peak_per_fish_sav)**2;

do i=1 to 60;

per_fish_sav_(i)=schd_(i)*peak_per_fish_sav;

Page 20
*note that per_fish_sq is 0 for linear models;

per_fish_sq_(i)=(per_fish_sav_(i))**2;

if per_fish_sav_(i) le fish_sav_WTP_threshold then CS_per_HH_&val._(i)=

(IP_fish_sav*per_fish_sav_(i))+(IP_fish_sav_sq*per_fish_sq_(i));

else if per_fish_sav_(i) > fish_sav_WTP_threshold then CS_per_HH_&val._(i)=

(IP_fish_sav*fish_sav_WTP_threshold)+(IP_fish_sav_sq*(fish_sav_WTP_threshold**2))

;

*calculate the peak WTP per household under using low, mean, and high implicit

prices;

max_CS_per_HH_&val.=max(of CS_per_HH_&val._1-CS_per_HH_&val._60);

*do not calculate WTP per household if the coefficients on fish saved or fish

saved squared

are not signficant;

if fish_sav_sig="no" or fish_sav_sq_sig="no" then do max_CS_per_HH_&val.=".";

end;

*calculate peak WTP per region without the compliance schedule by multiplying

peak WTP per household by the total number of households;

max_CS_reg_&val.=max_CS_per_HH_&val.*Num_households;

*do not calculate peak WTP per region if the coefficients on fish saved or fish

saved squared

are not signficant;

if fish_sav_sig="no" or fish_sav_sq_sig="no" then do max_CS_per_HH_&val.=".";

end;

*calculate a WTP value for each year of the compliance period;

CS_reg_&val._(i)=CS_per_HH_&val._(i)*Num_households;

*do not calculate WTP value for each year if the coefficients on fish saved or

fish saved squared

are not significant;

if fish_sav_sig="no" then CS_reg_&val._(i)=0;

if fish_sav_sq_sig="no" then CS_reg_&val._(i)=0;

end; drop i;

RUN;

proc sort data=CS_per_HH_&val. out=CS_per_HH_&val.;

by option sp_survey_region model;

RUN;

Data CS_spreg_&val.; set CS_per_HH_&val.;

keep option sp_survey_region model value fish_sav_sig fish_sav_sq_sig

num_households CS_reg_&val._1-CS_reg_&val._60;

RUN;

%mend;

%CS_per_HH(mean);%CS_per_HH(low);%CS_per_HH(high);

*calculate wtp per household for summary tables;

Data Household_WTP;

merge Cs_per_hh_low Cs_per_hh_mean Cs_per_hh_high;

by option sp_survey_region model;

keep option sp_survey_region model max_CS_per_HH_low max_CS_per_HH_mean

max_CS_per_HH_high;

RUN;

proc sort data=household_WTP out=household_WTP;

by sp_survey_region model;

RUN;

%export(household_wtp,"&outloc.&output",household_wtp);

*macro for high, low, and mean estimates of consumer surplus;

%macro CSCI_2(val);

proc sort data=CS_spreg_&val. out=CS_spreg_&val.; by option SP_survey_region;

RUN;

proc sort data=comp_calcs_6 out=comp_calcs_6; by option SP_survey_region; RUN;

Data CS_spreg_&val._1; merge CS_spreg_&val. comp_calcs_6; by option

SP_survey_region;

array schd_(60) schd_1-schd_60;

Page 21
array df3_(60) df3_1-df3_60;

array df7_(60) df7_1-df7_60;

array CS_reg_&val._(60) CS_reg_&val._1-CS_reg_&val._60;

array WTP3_&val._(60) WTP3_&val._1-WTP3_&val._60;

array WTP7_&val._(60) WTP7_&val._1-WTP7_&val._60;

do i=1 to 60;

df3_(i)=1/(1.03**(i-1+(&t0.-&d0.)));

df7_(i)=1/(1.07**(i-1+(&t0.-&d0.)));

WTP3_&val._(i)=CS_reg_&val._(i)*df3_(i);

WTP7_&val._(i)=CS_reg_&val._(i)*schd_(i)*df7_(i);

*calculate NPV for 3% discount rate and 7% discount rate;

WTP_NPV_&val._3=sum(of WTP3_&val._1-WTP3_&val._60);

WTP_NPV_&val._7=sum(of WTP7_&val._1-WTP7_&val._60);

Ann_WTP_&val._3=WTP_NPV_&val._3*&af_3.;

Ann_WTP_&val._7=WTP_NPV_&val._7*&af_7.;

end; drop i; RUN;

Data HH_survey_reg_1; set HH_survey_reg; RUN;

proc sort data=HH_survey_reg_1 out=HH_survey_reg_1; by SP_survey_region; RUN;

proc sort data=CS_spreg_&val._1 out=CS_spreg_&val._1; by SP_survey_region; RUN;

Data CS_spreg_&val._1; merge CS_spreg_&val._1 HH_survey_reg_1; by

SP_survey_region; RUN;

Proc sql; create table WTP_results_&val. as select

SP_survey_region label="SP Survey Region",

Option label="Option",

Num_households format=comma20. label="Number of Households",

model,

Ann_WTP_&val._3 format=dollar20. label="Ann WTP (&val.), 3%",

Ann_WTP_&val._7 format=dollar20. label="Ann WTP (&val.), 7%",

(Ann_WTP_&val._3/Num_households) as Ann_WTP_&val._3_HH format=dollar20.

label="Ann WTP per HH (&val.), 3%",

(Ann_WTP_&val._7/Num_households) as Ann_WTP_&val._7_HH format=dollar20.

label="Ann WTP per HH (&val.), 7%",

(Ann_WTP_&val._3/1000000) as Ann_WTP_&val._3_mm format=dollar20. label="Ann WTP

(&val.), 3%, in millions",

(Ann_WTP_&val._7/1000000) as Ann_WTP_&val._7_mm format=dollar20. label="Ann WTP

(&val.), 7%, in millions",

(Ann_WTP_&val._3/1000) as Ann_WTP_&val._3_thou format=dollar20. label="Ann WTP

(&val.), 3%, in thousands",

(Ann_WTP_&val._7/1000) as Ann_WTP_&val._7_thou format=dollar20. label="Ann WTP

(&val.), 7%, in thousands"

from CS_spreg_&val._1; quit;

proc sort data=CS_per_hh_&val. out=CS_per_hh_&val.; by SP_survey_region option;

RUN;

Data WTP_results_&val._regsum;

set WTP_results_&val.;

if SP_survey_region="National" then delete;

RUN;

Proc sql; create table WTP_results_&val._regsum as select

option,

num_households,

model,

sum(Ann_WTP_&val._3) as Ann_WTP_&val._3 format=dollar20. label="Ann WTP (&val.),

3%",

sum(Ann_WTP_&val._7) as Ann_WTP_&val._7 format=dollar20. label="Ann WTP (&val.),

7%",

sum(Ann_WTP_&val._3_mm) as Ann_WTP_&val._3_mm format=dollar20. label="Ann WTP

(&val.), 3%, in millions",

sum(Ann_WTP_&val._7_mm) as Ann_WTP_&val._7_mm format=dollar20. label="Ann WTP

(&val.), 7%, in millions",

sum(Ann_WTP_&val._3_thou) as Ann_WTP_&val._3_thou format=dollar20. label="Ann WTP

(&val.), 3%, in thousands",

sum(Ann_WTP_&val._7_thou) as Ann_WTP_&val._7_thou format=dollar20. label="Ann WTP

(&val.), 7%, in thousands"

from WTP_results_&val._regsum group by option, model;

Page 22
quit;

Data WTP_results_&val._regsum;

set WTP_results_&val._regsum;

SP_survey_region="RegTot";

RUN;

proc sort data=WTP_results_&val._regsum out=WTP_results_&val._regsum nodupkey;

by SP_Survey_Region Model Option;

RUN;

Data WTP_results_&val.;

set WTP_results_&val. WTP_results_&val._regsum;

RUN;

%mend;

%CSCI_2(low);%CSCI_2(high);%CSCI_2(mean);

%export(WTP_results_low,"&outloc.&output",WTP_results_low);

%export(WTP_results_mean,"&outloc.&output",WTP_results_mean);

%export(WTP_results_high,"&outloc.&output",WTP_results_high);

proc sort data=Ie_a1e_4x out=Ie_a1e_4x; by SP_survey_region option_name; RUN;

%export(Ie_a1e_4x,"&outloc.&output",a1e_per_change);

Proc sql; create table ie_SP_region as select

SP_survey_region label="SP Survey Region",

Option_name label="Option",

(ben_val_SPreg_adj/1000000) as ben_val_SPreg_adj_mm format=comma20.2 label="IM&E

Reduction (millions of a1e)",

(bas_val_SPreg_adj/1000000) as bas_val_SPreg_adj_mm format=comma20.2

label="Baseline IM&E (millions of a1e)",

peak_per_fish_sav format=comma20.2 label="Fish Saved (%)"

from ie_a1e_4; quit;

%export(ie_SP_region,"&outloc.&output",ime_SP_region);

%macro undisc(x);

Data CS_spreg_&x._1_out;

set CS_spreg_&x._1;

if model~="weighted_linear" then delete;

if option~="baseline" then delete;

keep sp_survey_region model option CS_reg_&x._46;

RUN;

proc sort data=CS_spreg_&x._1_out out=CS_spreg_&x._1_out;

by sp_survey_region model option;

RUN;

%mend;

%undisc(low); %undisc(mean); %undisc(high);

*import imp and ent files for the SP survey baseline;

%import(&facloc.&input3,BNB_IMP,i_only_SP);

%import(&facloc.&input3,BNB_ENT,e_only_SP);

*CACULATE THE FRACTION ASSOCIATED WITH IMPINGEMENT VS. ENTRAINMENT TO

SUPPORT THE ANALYSIS OF NEW UNITS***************************************;

Data imp_breakdown;

set i_only_SP;

if species_econ~="zz.all.species" then delete;

if comrec_type~="all.types" then delete;

if metric~="ae1" then delete;

if option_name~="option.tmn1" then delete;

drop option_name;

drop species_econ metric comrec_type benefit_value_regional value_basis;

if lossmode="imp" then baseline_value_imp=baseline_value_regional;

if lossmode~="imp" then baseline_value_imp=0;

drop lossmode baseline_value_regional;

RUN;

proc transpose data=imp_breakdown out=imp_breakdown_1;

ID region;

RUN;

Page 23
Data imp_breakdown_1;

set imp_breakdown_1;

drop _NAME_;

rename calif=calif_imp

greatlakes=greatlakes_imp

gulfmexico=gulfmexico_imp

inland=inland_imp

midatlantic=midatlantic_imp

northatlantic=northatlantic_imp

southatlantic=southatlantic_imp;

drop zz_allregions;

RUN;

Data ent_breakdown;

set e_only_SP;

if species_econ~="zz.all.species" then delete;

if comrec_type~="all.types" then delete;

if metric~="ae1" then delete;

if option_name~="option.tmn1" then delete;

drop option_name;

drop species_econ metric comrec_type benefit_value_regional value_basis;

if lossmode="ent" then baseline_value_ent=baseline_value_regional;

if lossmode~="ent" then baseline_value_ent=0;

drop lossmode baseline_value_regional;

RUN;

proc transpose data=ent_breakdown out=ent_breakdown_1;

ID region;

RUN;

Data ent_breakdown_1;

set ent_breakdown_1;

drop _NAME_;

rename calif=calif_ent

greatlakes=greatlakes_ent

gulfmexico=gulfmexico_ent

inland=inland_ent

midatlantic=midatlantic_ent

northatlantic=northatlantic_ent

southatlantic=southatlantic_ent;

drop zz_allregions;

RUN;

*create a crosswalk to map benefits regions to SP survey regions*************;

data IE_breakdown_crosswalk;

set ie_x;

if metric~="ae1" then delete;

if species_econ~="zz.all.species" then delete;

if comrec_type~="all.types" then delete;

if region="zz.allregions" then delete;

if option_name~="option.tmn1" then delete;

keep region full_base option_name;

option_name="baseline";

RUN;

proc sql;create table IE_breakdown_crosswalk_1 as select

a.option_name,

a.region,

b.state,

(a.full_base*b.frac_AIF) as full_base_state

from IE_breakdown_crosswalk as a left join state_aif_frac as b on

a.region=b.region;

quit;

proc sql; create table IE_breakdown_crosswalk_2 as select

a.option_name,

a.region,

a.state,

a.full_base_state,

b.SP_survey_region

from IE_breakdown_crosswalk_1 as a left join state_list as b on a.state=b.state;

quit;

proc sql; create table IE_breakdown_crosswalk_3 as select

Page 24
a.*,

sum(full_base_state) as full_base_benreg

from IE_breakdown_crosswalk_2 as a

group by region;

quit;

proc sql; create table IE_breakdown_crosswalk_4 as select

a.*,

sum(full_base_state) as full_base_benreg_bySP

from IE_breakdown_crosswalk_3 as a

group by region, SP_Survey_region;

quit;

proc sort data=IE_breakdown_crosswalk_4 out=IE_breakdown_crosswalk_4 nodupkey;

by region SP_survey_region;

RUN;

Data IE_breakdown_crosswalk_5;

set IE_breakdown_crosswalk_4;

SP_prop_of_benreg=full_base_benreg_bySP/full_base_benreg;

keep option_name region SP_survey_region SP_prop_of_benreg;

RUN;

proc sort data=IE_breakdown_crosswalk_5 out=IE_breakdown_crosswalk_5;

by SP_Survey_region;

RUN;

proc transpose data=IE_breakdown_crosswalk_5 out=IE_breakdown_crosswalk_6;

by SP_survey_region;

ID region;

RUN;

Data IE_breakdown_crosswalk_6;

set IE_breakdown_crosswalk_6;

if _N_=1 then set imp_breakdown_1;

if _N_=1 then set ent_breakdown_1;

if calif="." then calif=0;

if greatlakes="." then greatlakes=0;

if gulfmexico="." then gulfmexico=0;

if inland="." then inland=0;

if midatlantic="." then midatlantic=0;

if southatlantic="." then southatlantic=0;

if northatlantic="." then northatlantic=0;

drop _NAME_;

imp=calif*calif_imp+

greatlakes*greatlakes_imp+

gulfmexico*gulfmexico_imp+

inland*inland_imp+

midatlantic*midatlantic_imp+

northatlantic*northatlantic_imp+

southatlantic*southatlantic_imp;

ent=calif*calif_ent+

greatlakes*greatlakes_ent+

gulfmexico*gulfmexico_ent+

inland*inland_ent+

midatlantic*midatlantic_ent+

northatlantic*northatlantic_ent+

southatlantic*southatlantic_ent;

IE=imp+ent;

imp_percent=imp/IE;

ent_percent=ent/IE;

option="baseline";

keep option SP_survey_region imp_percent ent_percent;

RUN;

Data undiscounted_out;

merge CS_spreg_low_1_out CS_spreg_mean_1_out CS_spreg_high_1_out;

by sp_survey_region model option;

RUN;

proc sql;

create table undiscounted_out as select

a.*,b.*,

(a.CS_reg_low_46*b.imp_percent) as CS_reg_low_46_imp,

(a.CS_reg_low_46*b.ent_percent) as CS_reg_low_46_ent,

(a.CS_reg_mean_46*b.imp_percent) as CS_reg_mean_46_imp,

(a.CS_reg_mean_46*b.ent_percent) as CS_reg_mean_46_ent,

Page 25
(a.CS_reg_high_46*b.imp_percent) as CS_reg_high_46_imp,

(a.CS_reg_high_46*b.ent_percent) as CS_reg_high_46_ent

from undiscounted_out as a left join IE_breakdown_crosswalk_6 as b

on a.SP_survey_Region=b.SP_survey_region and a.option=b.option;

quit;

%export(undiscounted_out,"&outloc.&output",undiscounted_out);

Page 26