Document ID: EPA-HQ-OW-2008-0465-1863
Agency: epa
Document Type: Supporting & Related Material
Title: 
Posted Date: 2009-12-07T05:00Z

README: Overview of SAS Program “Krinsky and Robb Sensitivity Analysis
by Reach Including WQ Calcs.sas”

Author: Abt Associates Inc.

Date: November 23, 2009

This document describes the input files, output files, and steps taken
in the SAS program “Krinsky and Robb Sensitivity Analysis by Reach
Including WQ Calcs.sas” (DNC 6-7907)

PLEASE REMOVE THE DCN X-XXX PART FROM THE FILE NAMES PRIOR TO RUNNING
ANY PROGRAMS

Input Files:

metadataforCandD9.10.09.csv (DCN 6-7902)

WTPcoef v3.xls  (DCN  6-7906)

predict_base_revised.sas7bdat (DCN 6-7400)

predict_base_tn_tp.sas7bdat (DCN 6-7401)

predict_Option1_092809.sas7bdat (DCN 6-7402)

predict_Option2_092809.sas7bdat (DCN 6-7403)

predict_Option3_092809.sas7bdat (DCN 6-7404)

predict_Option4_092809.sas7bdat (DCN 6-7405)

predict_Zero_loadings_092909.sas7bdat (DCN 6-7406)

Output Files:

Krinsky and Robb Sensitivity Analysis WTP by Reach Including WQ
Calcs_Final Rule.lst (DNC 6-7908)

SPARROW Data for AV Cost 16NOV09.xls (DNC 6-7909)

DCP Summary Tables 16NOV09.xls (DNC 6-7910)

WTP per Reach 16NOV09.xls (DNC 6-7911)

WQ Reg miles Improv 16NOV09.xls  (DNC 6-7912)

TSS_TN_TP_base.sas7bdat  (DCN 6-7913)

TSS_TN_TP_option_1.sas7bdat  (DCN 6-7914)

TSS_TN_TP_option_2.sas7bdat  (DCN 6-7915)

TSS_TN_TP_option_3.sas7bdat  (DCN 6-7916)

TSS_TN_TP_option_4.sas7bdat  (DCN 6-7917)

TSS_TN_TP_option_5.sas7bdat  (DCN 6-7918)

Est_base.sas7bdat  (DCN 6-7919)

Est_option_1.sas7bdat  (DCN 6-7920)

Est_option_2.sas7bdat  (DCN 6-7921)

Est_option_3.sas7bdat  (DCN 6-7922)

Est_option_4.sas7bdat  (DCN 6-7923)

Est_option_5.sas7bdat  (DCN 6-7924)



This SAS program has 9 main Sections which perform a Different Function:

Using the meta-data to estimate coefficient values for the meta-analysis
using a trans-log model.  Then using the Krinsky and Robb (1986)
bootstrapping approach 1,000 random coefficient values are calculated
for each variable. 

Importing and cleaning TSS, nutrient, and other data for the baseline
baseline, the four regulatory options, and the hypothetical no discharge
scenario.

 

Generating input files to be used in the avoided cost analysis to
estimate the benefits for drinking water and navigation.

Estimating the change in TSS and nutrient concentrations under all
regulatory options for both freshwater reaches and estuaries.  

 

Creating tables summarizing the DCP values for estuaries, and the number
of estuaries using DCP in the analysis. 

 Generating input files for  “C&D TSS, TN, and TP Summary Stats.sas
(DCN X-XXX) 

Calculating baseline and change in water quality for each RF1 reach. 
Water quality values are calculated separately for freshwater and
saltwater reaches

Using the meta-analysis results and the water quality data to calculate
household willingness to pay and total benefits by region for
improvements in water quality which result from the C&D regulation

Using the water quality data to calculate the total number of river
miles which fall in to each baseline water quality level.  And then
using the water quality data to calculate the total number of miles
which improve by region and option under each of the 12 analytical
scenarios.  

Meta-Analysis and Krinsky and Robb Bootstrapping 

Coefficient values for the WTP function are estimated using
meta-analysis using a trans-log model.  The meta-data is imported from
the ‘metadataforCandD9.10.09.csv ’.  The Proc Mixed command is used
to estimate the regression results.  

The program then estimates 1,000 random coefficient estimates using the
Krinsky and Robb (1986) bootstrapping approach.  WTP values are
calculated using the variance covariance matrix of the estimated
coefficient values in the meta-analysis.

The output file ‘Updated Krinsky and Robb Sensitivity Analysis by
Reach Including WQ Calcs.lst’ contains the results of the trans-log
meta-analysis regression.  It also contains the variance-covariance
matrix of the estimated coefficients.  

Data Importation and Cleaning 

Baseline SPARROW data is imported from
“predict_base_revised.sas7bdat” and
“predict_base_tn_tp.sas7bdat”.  These datasets contain baseline TN,
TP, and SCC concentrations.  The data is cleaned and merged with
demographic, other WQ data, and TN/TSS and TP/TSS ratios are imported
from “WTPcoef v3 10-5-09.xls”.  The 95th baseline SCC concentration
is calculated and all concentrations above this value are considered
outliers and are replaced with the 95th percentile concentration.  The
option specific SPARROW data is then imported and baseline TSS values
are calculated.    

    

Generating Input files for the Avoided Cost Analysis

Several macros are used to calculate input data for the drinking water
and navigational benefits analysis is calculated.  Average total
incremental yield is calculated at the state level, and total res decay
is calculated by EPA region and at the state level.  Average incremental
yield is also calculated as a percentage of the baseline value.

Calculating change in TSS, TN, and TP under all Regulatory Options 

TSS and change in TSS from the baseline is calculated for all regulatory
options.  In addition, change in TN and TP from the baseline are
calculated for each regulatory option.  Change in TN and TP are
calculated as a function of change in TSS, these ratios are imported
from the Excel file 

Generating Input files for the “TSS, TN, and TP Summary Stats_Final
Rule.sas” SAS program

Next input files for the SAS program “TSS, TN, and TP Summary
Stats_Final Rule.sas” (DCN 6-7703) are created containing TSS, TN, and
TP data for all reaches in the analysis under the baseline and each
option.  Separate files are generated for freshwater and estuarine
reaches.

Baseline and Change in Water Quality Calculations

Water quality is then calculated for each reach under the baseline and
all four options, using either the freshwater or an estuary WQI
calculation.  An eco region specific subindex curve is used to calculate
the TSS subindex for all reaches.  The subindex curve is inputted from
“WTPcoef v3 10-5-09.xls”.The freshwater reach and saltwater reach
datasets are combined.  Change in water quality is calculated for each
reach under all fout regulatory options.      

     

Average State Willingness to Pay for Water Quality and benefits per
reach calculations 

 (WQ < 26, 26 ≤ WQ < 50, 50 ≤ WQ < 70, and WQ ≥ 70), and change in
water quality (0.01≤ ∆WQ < 0.1, 0.1≤ ∆WQ < .5, and   ∆WQ ≥
.5).  

The water quality data is then combined with the coefficient estimates
created by the Krinsky and Robb bootstrapping approach, and state
demographic data to estimate average state WTP for each of the 12
analytical scenarios under each option.  State demographic data such as
the number of households and average household income is inputted from
the file “WTPcoef v3 10-5-09.xls”.  Separate WTP calculations are
performed for freshwater and saltwater reaches.  Means and 90%
confidence levels are calculated for each state under all analytical
scenarios and options.  

Benefits are then calculated at the reach level.  Each reach is assigned
a WTP value based on reach type (saltwater or freshwater), location,
baseline water quality, and change in water quality.  Benefits per reach
are then calculated by multiplying the WTP value by the number of
households in a state, and the
percenta敧漠⁦瑳瑡⁥業敬⁳桷捩⁨牡⁥潣灭楲敳⁤祢愠
朠癩湥爠慥档‮删慥档氠癥汥戠湥晥瑩⁳牡⁥条牧来瑡
摥琠⁯桴⁥敲楧湯污氠癥汥‮删獥汵獴愠敲攠灸牯整⁤
湩潴琠敨映汩⁥垓偔瀠牥删慥档ㄠ丶噏㤰砮獬⺔††ഠ

 h

2

3

$

%

1

2

3

@

A

•

›

¡

¢

£

Ç

È

Ì

Í

Ó

Ô

Õ

é

ê

ó

ô

ø

ù

ÿ

93

A

£

Õ

$

N

n

’

¶

Ú

þ



"

#

$

6

A

E

L

M

N

V

a

e

l

m

n

z

…

‰

‘

’

ž

©

´

µ

¶

Â

Í

Ñ

Ø

Ù

Ú

æ

ñ

õ

ü

ý

Iý

þ

 h°

 h°

)

hå3

hå3

Average State Willingness to Pay for Water Quality and benefits per
reach calculations 

The program then calculates the total number of miles by each region
that fall into each of the four levels of baseline water quality, the
three water quality improvement levels under each option, and the 12
analytical scenarios by option.  The results are exported into the file
“WQ Reg miles Improv 16NOV09.xls”.