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

**

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

**

*Author: Ryan Stapler, Abt Associates Inc.

*Created: 02/16/2013;

*Updated: 05/02/2014

*Purpose: This is a SAS program which estimates benefits for new units. It

extrapolates from EPA's estimates of baseline IM&E losses and baseline benefits

from the analysis of existing units.

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

**

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

**;

*SPECIFY INPUT AND OUTPUT

FILES****************************************************;

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

%let d=s; *(C or H or S drive);

%let facloc=&d.:\Projects\Tyche\WA 4-05 (316b)\Benefits

Estimation\Final_05.02.14\;

%let mainloc=&facloc.New_Units\;

*The name of the facility data input file is;

%let fac_input=Facility_Inputs_and_Weights_01.25.14.xls;

*The name of the new units input file is;

%let in_nu=New_Units_Benefits_Inputs_03.20.14.xlsx;

*The name of the IE input files is;

%let input2=IE_outputs_25JAN14_adjusted_for_state_regs.xls;

*specify weighted baseline flow for impingement and entrainment separately;

%let imp_wflow=163154.6;

%let ent_wflow=184721.1;

*specify flow reductions under new units regulatory options in millions of

gallons

per day (mgd);

%let opt_A=1282;

%let opt_B=462;

%let opt_C=68;

%let opt_D=7;

*define the years receiving flow reductions;

%let year_start=2014;

%let year_end=2059;

*discounting assumptions;

%let yrsc=51; *(yrsc is the annualization period);

%let yrba=2013; *(yrba is the base year for discounting);

%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 output files are;

%let output=New_Units_Outputs_&sysdate..xls;

*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 OUT= WORK.&out

DATAFILE= "&infile"

DBMS=EXCEL REPLACE;

SHEET="&insheet.$";

GETNAMES=YES;

MIXED=NO;

Page 1
SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

%mend;

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

*import data from excel;

*import facility data;

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

*import model weights;

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

*import IE data;

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

*import entrainment data;

%import(&facloc.&input2.,bnb_ent,e);

*import impingement data;

%import(&facloc.&input2.,bnb_imp,i);

*import the undiscounted annual benefits by region and category;

%import(&mainloc.&in_nu.,annual_undiscounted,annual_undiscounted);

%macro compile(l);

data &l._1;

format region $20.;

set &l.;

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

if region~="zz.allregions" then delete;

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

drop species_econ value_basis lossmode benefit_value_regional option_name;

if region="zz.allregions" then do region="National"; end;

if metric="lost.lbs" then delete;

if metric="pf.lbs" and comrec_type~="all.types" then delete;

if metric="yf.lbs" and comrec_type~="all.types" then delete;

if metric="ae1.lbs" then delete;

if comrec_type="com.types" then do comrec_type="comrec.types"; end;

if comrec_type="rec.types" then do comrec_type="comrec.types"; end;

rename baseline_value_regional=baseline_value;

RUN;

proc sql;

create table &l._2 as select

region,

metric,

comrec_type,

sum(baseline_value) as baseline_value

from &l._1 group by region, metric, comrec_type;

quit;

Data &l._3;

format val $20.;

set &l._2;

if metric="ae1" and comrec_type="all.types" then val="a1e_all";

if metric="ae1" and comrec_type="comrec.types" then val="a1e_comrec";

if metric="ae1" and comrec_type="forage.types" then val="a1e_forage";

if metric="forgo.harv.n.fish" and comrec_type="all.types" then delete;

if metric="forgo.harv.n.fish" and comrec_type="comrec.types" then val="fh_fish";

if metric="losses.baselined" and comrec_type="all.types" then val="org_all";

if metric="losses.baselined" and comrec_type="comrec.types" then

val="org_comrec";

if metric="losses.baselined" and comrec_type="forage.types" then

val="org_forage";

if metric="pf.lbs" and comrec_type="all.types" then val="pf_all";

Page 2
if metric="yf.lbs" and comrec_type="all.types" then val="yf_all";

drop metric comrec_type region;;

RUN;

proc transpose data=&l._3 out=&l._4;

var baseline_value;

id val;

RUN;

Data &l._4;

set &l._4;

drop _NAME_;

region="National";

Run;

%mend;

%compile(i); %compile(e);

*calculate IM&E per mgd values by dividing impingement and entrainment estimates

by weighted flow -

caculations are separate for impingement and entrainment;

Data i_per_mgd_1;

set i_4;

a1e_all_per_mgd=(a1e_all/&imp_wflow.);

a1e_comrec_per_mgd=(a1e_comrec/&imp_wflow.);

a1e_forage_per_mgd=(a1e_forage/&imp_wflow.);

fh_fish_per_mgd=(fh_fish/&imp_wflow.);

org_all_per_mgd=(org_all/&imp_wflow.);

org_comrec_per_mgd=(org_comrec/&imp_wflow.);

org_forage_per_mgd=(org_forage/&imp_wflow.);

pf_all_per_mgd=(pf_all/&imp_wflow.);

yf_all_per_mgd=(yf_all/&imp_wflow.);

RUN;

Data e_per_mgd_1;

set e_4;

a1e_all_per_mgd=(a1e_all/&ent_wflow.);

a1e_comrec_per_mgd=(a1e_comrec/&ent_wflow.);

a1e_forage_per_mgd=(a1e_forage/&ent_wflow.);

fh_fish_per_mgd=(fh_fish/&ent_wflow.);

org_all_per_mgd=(org_all/&ent_wflow.);

org_comrec_per_mgd=(org_comrec/&ent_wflow.);

org_forage_per_mgd=(org_forage/&ent_wflow.);

pf_all_per_mgd=(pf_all/&ent_wflow.);

yf_all_per_mgd=(yf_all/&ent_wflow.);

RUN;

*calculate monetized benefits per mgd by dividing impingement and entrainment

benefits estimates by weighted flow -

caculations are separate for impingement and entrainment;

%macro compile1(l);

Data annual_undiscounted_&l.;

set annual_undiscounted;

if lossmode~="&l." then delete;

RUN;

%mend;

%compile1(i); %compile1(e);

Data annual_undiscounted_mgd_i;

set annual_undiscounted_i;

com_per_mgd=(com/&imp_wflow.);

rec_low_per_mgd=(rec_low/&imp_wflow.);

rec_mean_per_mgd=(rec_mean/&imp_wflow.);

rec_high_per_mgd=(rec_high/&imp_wflow.);

TE_per_mgd=(TE/&imp_wflow.);

BT_per_mgd=(BT/&imp_wflow.);

SP_survey_low_per_mgd=(SP_survey_low/&imp_wflow.);

SP_survey_mean_per_mgd=(SP_survey_mean/&imp_wflow.);

SP_survey_high_per_mgd=(SP_survey_high/&imp_wflow.);

RUN;

Data annual_undiscounted_mgd_e;

Page 3
set annual_undiscounted_e;

com_per_mgd=(com/&ent_wflow.);

rec_low_per_mgd=(rec_low/&ent_wflow.);

rec_mean_per_mgd=(rec_mean/&ent_wflow.);

rec_high_per_mgd=(rec_high/&ent_wflow.);

TE_per_mgd=(TE/&ent_wflow.);

BT_per_mgd=(BT/&ent_wflow.);

SP_survey_low_per_mgd=(SP_survey_low/&ent_wflow.);

SP_survey_mean_per_mgd=(SP_survey_mean/&ent_wflow.);

SP_survey_high_per_mgd=(SP_survey_high/&ent_wflow.);

RUN;

*specify the analysis period;

Data analysis_period;

input year;

datalines;

2012

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

2023

2024

2025

2026

2027

2028

2029

2030

2031

2032

2033

2034

2035

2036

2037

2038

2039

2040

2041

2042

2043

2044

2045

2046

2047

2048

2049

2050

2051

2052

2053

2054

2055

2056

2057

2058

2059

2060

2061

2062

2063

2064

Page 4
2065

2066

2067

2068

2069

2070

;

RUN;

*calculate the new units flow reductions for each year of the analysis period;

Data nu_flow_mgd;

set analysis_period;

if year ge &year_start. and year le &year_end. then do include=1; end;

if year < &year_start. or year > &year_end. then do include=0; end;

flow_period=(year-&year_start.)+1;

opt_A_mgd=flow_period*&opt_a.*include;

opt_B_mgd=flow_period*&opt_b.*include;

opt_C_mgd=flow_period*&opt_c.*include;

opt_D_mgd=flow_period*&opt_d.*include;

RUN;

*calculate an adjustment factor to account for the biological lag for the

realization of benefits

associated with the new units flow reductions;

Data flow_lag_adj;

set nu_flow_mgd;

drop opt_A_mgd opt_B_mgd opt_C_mgd opt_D_mgd;

if flow_period<0 then flow_period=0;

array period_(60) period_1-period_60;

do i=1 to 60;

if flow_period ge i then period_(i)=1; else period_(i)=0;

*specify initial biological lag;

if flow_period=i then period_(i)=0.1;

if flow_period=(i+1) then period_(i)=0.2;

if flow_period=(i+2) then period_(i)=0.8;

if flow_period=(i+3) then period_(i)=0.9;

if flow_period=(i+4) then period_(i)=0.95;

if flow_period=(i+5) then period_(i)=1;

if year>&year_end. then period_(i)=0;

end;

drop i;

*sum across periods;

sum_periods=sum(of period_1-period_60);

drop period_1-period_60;

if year le &year_end. then adj_SP=flow_period;

if year > &year_end. then adj_SP=0;

RUN;

proc sql;

create table flow_lag_adj as select

year,

flow_period,

sum_periods,

max(sum_periods) as max_harv,

adj_SP,

max(adj_SP) as max_BT

from flow_lag_adj;

quit;

Data flow_lag_adj;

set flow_lag_adj;

if year le &year_end. then adj_harv=sum_periods;

if year le &year_end. then adj_BT=adj_SP;

drop sum_periods;

*set biological ramp down for commercial, recreational, and T&E;

if year=(&year_end.+1) then adj_harv=max_harv*.9;

if year=(&year_end.+2) then adj_harv=max_harv*.8;

if year=(&year_end.+3) then adj_harv=max_harv*.2;

if year=(&year_end.+4) then adj_harv=max_harv*.1;

if year=(&year_end.+5) then adj_harv=max_harv*.05;

if year ge (&year_end.+6) then adj_harv=max_harv*0;

Page 5
*set biological ramp down for the benefits transfer values;

if year=(&year_end.+1) then adj_BT=max_BT*.83;

if year=(&year_end.+2) then adj_BT=max_BT*.67;

if year=(&year_end.+3) then adj_BT=max_BT*.5;

if year=(&year_end.+4) then adj_BT=max_BT*.33;

if year=(&year_end.+5) then adj_BT=max_BT*.17;

if year ge (&year_end.+6) then adj_BT=max_BT*0;

drop max_harv max_BT;

RUN;

*calculate the peak IM&E reductions under each new units option based on annual

flow reduction

and the number of periods;

*l is the lossmode (i or e). ta is an adjustment factor to account for baseline

technology at new units.

Calcs assume that new units would have IM tech with 75% reduction w/o the new

units options, therefore the

adjustment factor is specified at 0.25 (i.e., 1-0.75);

%macro compile2(l,ta);

Data &l._nu_opt_A;

format option $2.;

set &l._per_mgd_1;

a1e_all_max=a1e_all_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

a1e_comrec_max=a1e_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

a1e_forage_max=a1e_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

fh_fish_max=fh_fish_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

org_all_max=org_all_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

org_comrec_max=org_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

org_forage_max=org_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

yf_all_max=yf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

pf_all_max=pf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_A.*&ta.;

option="A";

RUN;

Data &l._nu_opt_B;

format option $2.;

set &l._per_mgd_1;

a1e_all_max=a1e_all_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

a1e_comrec_max=a1e_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

a1e_forage_max=a1e_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

fh_fish_max=fh_fish_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

org_all_max=org_all_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

org_comrec_max=org_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

org_forage_max=org_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

yf_all_max=yf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

pf_all_max=pf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_B.*&ta.;

option="B";

RUN;

Data &l._nu_opt_C;

format option $2.;

set &l._per_mgd_1;

a1e_all_max=a1e_all_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

a1e_comrec_max=a1e_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

a1e_forage_max=a1e_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

fh_fish_max=fh_fish_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

org_all_max=org_all_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

org_comrec_max=org_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

org_forage_max=org_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

yf_all_max=yf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

pf_all_max=pf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_C.*&ta.;

option="C";

RUN;

Data &l._nu_opt_D;

format option $2.;

set &l._per_mgd_1;

a1e_all_max=a1e_all_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

Page 6
a1e_comrec_max=a1e_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

a1e_forage_max=a1e_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

fh_fish_max=fh_fish_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

org_all_max=org_all_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

org_comrec_max=org_comrec_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

org_forage_max=org_forage_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

yf_all_max=yf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

pf_all_max=pf_all_per_mgd*(&year_end.-&year_start.+1)*&opt_D.*&ta.;

option="D";

RUN;

data &l._peak_by_option;

lossmode="&l.";

set

&l._nu_opt_A

&l._nu_opt_B

&l._nu_opt_C

&l._nu_opt_D;

drop a1e_all_per_mgd a1e_comrec_per_mgd a1e_forage_per_mgd fh_fish_per_mgd

org_all_per_mgd org_comrec_per_mgd

org_forage_per_mgd yf_all_per_mgd pf_all_per_mgd;

RUN;

%mend;

%compile2(i,.25); %compile2(e,1);

*compile total across impingement and entrainment;

Data peak_by_option;

set i_peak_by_option

e_peak_by_option;

RUN;

proc sql;

create table peak_by_option as select

option,

"ie" as lossmode,

sum(a1e_all_max) as a1e_all_max,

sum(a1e_comrec_max) as a1e_comrec_max,

sum(a1e_forage_max) as a1e_forage_max,

sum(fh_fish_max) as fh_fish_max,

sum(org_all_max) as org_all_max,

sum(org_comrec_max) as org_comrec_max,

sum(org_forage_max) as org_forage_max,

sum(yf_all_max) as yf_all_max,

sum(pf_all_max) as pf_all_max

from peak_by_option group by option;

quit;

%macro out(l);

Data &l._peak_by_option_1;

set &l._peak_by_option;

keep lossmode option

a1e_all_max a1e_comrec_max a1e_forage_max fh_fish_max org_all_max org_comrec_max

org_forage_max yf_all_max pf_all_max;

RUN;

%mend;

%out(i); %out(e);

*create a summary table of peak IM&E reductions for new units by option and

lossmode

and export to excel;

Data peak_by_option_l;

format lossmode $4.;

set i_peak_by_option_1 e_peak_by_option_1 peak_by_option;

RUN;

%export(peak_by_option_l,"&mainloc.&output",peak_by_option);

*create a summary table of IM&E reductions per mgd and export to excel;

%macro out1(l);

Data &l._per_mgd_1x;

lossmode="&l.";

set &l._per_mgd_1;

keep lossmode a1e_all_per_mgd a1e_comrec_per_mgd a1e_forage_per_mgd

Page 7
fh_fish_per_mgd org_all_per_mgd

org_comrec_per_mgd org_forage_per_mgd pf_all_per_mgd yf_all_per_mgd;

RUN;

%mend;

%out1(i);%out1(e);

Data bio_per_mgd; set i_per_mgd_1x e_per_mgd_1x; RUN;

%export(bio_per_mgd,"&mainloc.&output",bio_per_mgd);

*CALCULATE DISCOUNTED BENEFITS UNDER NEW UNITS

OPTIONS*****************************;

Data flow_lag_adj_A; format option $2.; set flow_lag_adj; option="A"; RUN;

Data flow_lag_adj_B; format option $2.; set flow_lag_adj; option="B"; RUN;

Data flow_lag_adj_C; format option $2.; set flow_lag_adj; option="C"; RUN;

Data flow_lag_adj_D; format option $2.; set flow_lag_adj; option="D"; RUN;

*calculate benefits by loss mode incorporate a tech adjustment to account for

baseline

IM tech at new units - tech adjustment set to 0.25 for i and 1.0 for e;

%macro compile3(l,ta);

Data benefits_opts_&l.;

set flow_lag_adj_A

flow_lag_adj_B

flow_lag_adj_C

flow_lag_adj_D;

if _N_=1 then set annual_undiscounted_mgd_&l.;

if option="A" then annual_flow=&opt_A.;

if option="B" then annual_flow=&opt_B.;

if option="C" then annual_flow=&opt_C.;

if option="D" then annual_flow=&opt_D.;

RUN;

Data benefits_opts_&l._1;

set benefits_opts_&l.;

comben_adj=annual_flow*com_per_mgd*adj_harv*&ta.;

recben_low_adj=annual_flow*rec_low_per_mgd*adj_harv*&ta.;

recben_mean_adj=annual_flow*rec_mean_per_mgd*adj_harv*&ta.;

recben_high_adj=annual_flow*rec_high_per_mgd*adj_harv*&ta.;

TEben_adj=annual_flow*TE_per_mgd*adj_harv*&ta.;

BTben_adj=annual_flow*BT_per_mgd*adj_BT*&ta.;

SP_surveyben_low_adj=annual_flow*SP_survey_low_per_mgd*adj_SP*&ta.;

SP_surveyben_mean_adj=annual_flow*SP_survey_mean_per_mgd*adj_SP*&ta.;

SP_surveyben_high_adj=annual_flow*SP_survey_high_per_mgd*adj_SP*&ta.;

RUN;

Data benefits_opts_&l._2;

set benefits_opts_&l._1;

%macro b(cat);

&cat._3=&cat._adj*(1/(1.03**(year-&yrba.)));

&cat._7=&cat._adj*(1/(1.07**(year-&yrba.)));

%mend;

%b(comben);

%b(recben_low);

%b(recben_mean);

%b(recben_high);

%b(TEben);

%b(BTben);

%b(SP_surveyben_low);

%b(SP_surveyben_mean);

%b(SP_surveyben_high);

RUN;

*calculate annualized benefits in 1,000s of dollars;

proc sql;

create table benefits_&l._ann_1000s as select

"&l." as lossmode,

option,

(sum(comben_3)*&af_3.)/1000 as comben_ann3,

(sum(recben_low_3)*&af_3.)/1000 as recben_low_ann3,

(sum(recben_mean_3)*&af_3.)/1000 as recben_mean_ann3,

(sum(recben_high_3)*&af_3.)/1000 as recben_high_ann3,

(sum(TEben_3)*&af_3.)/1000 as TEben_ann3,

Page 8
(sum(BTben_3)*&af_3.)/1000 as BTben_ann3,

(sum(SP_surveyben_low_3)*&af_3.)/1000 as SP_surveyben_low_ann3,

(sum(SP_surveyben_mean_3)*&af_3.)/1000 as SP_surveyben_mean_ann3,

(sum(SP_surveyben_high_3)*&af_3.)/1000 as SP_surveyben_high_ann3,

(sum(comben_7)*&af_7.)/1000 as comben_ann7,

(sum(recben_low_7)*&af_7.)/1000 as recben_low_ann7,

(sum(recben_mean_7)*&af_7.)/1000 as recben_mean_ann7,

(sum(recben_high_7)*&af_7.)/1000 as recben_high_ann7,

(sum(TEben_7)*&af_7.)/1000 as TEben_ann7,

(sum(BTben_7)*&af_7.)/1000 as BTben_ann7,

(sum(SP_surveyben_low_7)*&af_7.)/1000 as SP_surveyben_low_ann7,

(sum(SP_surveyben_mean_7)*&af_7.)/1000 as SP_surveyben_mean_ann7,

(sum(SP_surveyben_high_7)*&af_7.)/1000 as SP_surveyben_high_ann7

from benefits_opts_&l._2 group by option;

quit;

%mend;

%compile3(i,.25); %compile3(e,1);

Data benefits_ie_ann_1000s;

set benefits_i_ann_1000s

benefits_e_ann_1000s;

RUN;

proc sql;

create table benefits_ie_ann_1000s as select

"ie" as lossmode,

option,

sum(comben_ann3) as comben_ann3,

sum(recben_low_ann3) as recben_low_ann3,

sum(recben_mean_ann3) as recben_mean_ann3,

sum(recben_high_ann3) as recben_high_ann3,

sum(TEben_ann3) as TEben_ann3,

sum(BTben_ann3) as BTben_ann3,

sum(SP_surveyben_low_ann3) as SP_surveyben_low_ann3,

sum(SP_surveyben_mean_ann3) as SP_surveyben_mean_ann3,

sum(SP_surveyben_high_ann3) as SP_surveyben_high_ann3,

sum(comben_ann7) as comben_ann7,

sum(recben_low_ann7) as recben_low_ann7 ,

sum(recben_mean_ann7) as recben_mean_ann7,

sum(recben_high_ann7) as recben_high_ann7,

sum(TEben_ann7) as TEben_ann7,

sum(BTben_ann7) as BTben_ann7,

sum(SP_surveyben_low_ann7) as SP_surveyben_low_ann7,

sum(SP_surveyben_mean_ann7) as SP_surveyben_mean_ann7,

sum(SP_surveyben_high_ann7) as SP_surveyben_high_ann7

from benefits_ie_ann_1000s group by option;

quit;

*compile benefits results for i, e, and ie for each new units option and export

to excel;

Data benefits_ann_1000s;

format lossmode $4.;

set benefits_i_ann_1000s benefits_e_ann_1000s benefits_ie_ann_1000s;

RUN;

%export(benefits_ann_1000s,"&mainloc.&output",benefits_ann_1000s);

*compile undiscounted benefits per mgd and export to excel;

%macro out2(l);

Data annual_undiscounted_mgd_&l.x;

lossmode="&l.";

set annual_undiscounted_mgd_&l.;

keep lossmode com_per_mgd rec_low_per_mgd rec_mean_per_mgd rec_high_per_mgd

TE_per_mgd BT_per_mgd

SP_survey_low_per_mgd SP_survey_mean_per_mgd SP_survey_high_per_mgd;

RUN;

%mend;

%out2(i); %out2(e);

Data benefits_per_mgd;

set annual_undiscounted_mgd_ix

annual_undiscounted_mgd_ex;

RUN;

%export(benefits_per_mgd,"&mainloc.&output",benefits_per_mgd);

Page 9
*create a table of time profile of undiscounted benefits, and benefits discount

at 3 percent and 7 percent;

%macro tp(l);

Data benefits_opts_&l._tp;

set benefits_opts_&l._2;

drop flow_period adj_SP adj_harv adj_BT lossmode region

com rec_low rec_mean rec_high TE BT SP_survey_low SP_survey_mean SP_survey_high

com_per_mgd rec_low_per_mgd rec_mean_per_mgd rec_high_per_mgd TE_per_mgd

BT_per_mgd

SP_survey_low_per_mgd SP_survey_mean_per_mgd SP_survey_high_per_mgd annual_flow;

rename

comben_adj=com_&l.

recben_low_adj=rec_low_&l.

recben_mean_adj=rec_mean_&l.

recben_high_adj=rec_high_&l.

TEben_adj=TE_&l.

BTben_adj=BT_&l.

SP_surveyben_low_adj=SP_low_&l.

SP_surveyben_mean_adj=SP_mean_&l.

SP_surveyben_high_adj=SP_high_&l.

comben_3=com_3_&l.

recben_low_3=rec_low_3_&l.

recben_mean_3=rec_mean_3_&l.

recben_high_3=rec_high_3_&l.

TEben_3=TE_3_&l.

BTben_3=BT_3_&l.

SP_surveyben_low_3=SP_low_3_&l.

SP_surveyben_mean_3=SP_mean_3_&l.

SP_surveyben_high_3=SP_high_3_&l.

comben_7=com_7_&l.

recben_low_7=rec_low_7_&l.

recben_mean_7=rec_mean_7_&l.

recben_high_7=rec_high_7_&l.

TEben_7=TE_7_&l.

BTben_7=BT_7_&l.

SP_surveyben_low_7=SP_low_7_&l.

SP_surveyben_mean_7=SP_mean_7_&l.

SP_surveyben_high_7=SP_high_7_&l.;

RUN;

%mend;

%tp(e); %tp(i);

Data benefits_opts_tp;

merge benefits_opts_e_tp benefits_opts_i_tp;

by option year;

%macro tp(c);

&c.=&c._e+&c._i;

&c._3=&c._3_e+&c._3_i;

&c._7=&c._7_e+&c._7_i;

drop &c._e &c._i &c._3_e &c._3_i &c._7_e &c._7_i

%mend;

%tp(com);

%tp(rec_low);

%tp(rec_mean);

%tp(rec_high);

%tp(TE);

%tp(BT);

%tp(SP_low);

%tp(SP_mean);

%tp(SP_high);

RUN;

data benefits_opts_tp_1;

set benefits_opts_tp;

*low - reflect low recreational values;

tot_low=com+rec_low+TE+BT;

tot_low_3=com_3+rec_low_3+TE_3+BT_3;

tot_low_7=com_7+rec_low_7+TE_7+BT_7;

Page 10
*mean - reflect mean recreational values;

tot_mean=com+rec_mean+TE+BT;

tot_mean_3=com_3+rec_mean_3+TE_3+BT_3;

tot_mean_7=com_7+rec_mean_7+TE_7+BT_7;

*high - reflect high recreational values;

tot_high=com+rec_high+TE+BT;

tot_high_3=com_3+rec_high_3+TE_3+BT_3;

tot_high_7=com_7+rec_high_7+TE_7+BT_7;

drop

com rec_low rec_mean rec_high TE BT

com_3 rec_low_3 rec_mean_3 rec_high_3 TE_3 BT_3

com_7 rec_low_7 rec_mean_7 rec_high_7 TE_7 BT_7;

RUN;

%export(benefits_opts_tp_1,"&mainloc.&output",time_profiles);

Page 11