data_combine_03.21.11.txt

Author: Abt Associates Inc.

Created: 8/13/2010

Updated: 3/21/2011;

Purpose: This is a SAS program which reorganizes the I&E data to combine separate

outputs for manufacturers and generators;

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

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

%let mainloc=&d.:\ERD\TYCHE Contract\Assignments\WA 2-15 (316b Benefits & SP

Study)\Task 5 - Benefits Analytic Support\IE_032111\data_edits\;

%let inloc=&mainloc.Inputs\;

%let outloc=&mainloc.Outputs\;

libname outloc "&outloc";

*assumptions;

*The name of the input file is;

%let input=baselines.and.benefits.Mon.Mar.21.2011_opt4_edit.xls;

*The name of the output files are;

%let output=IE_outputs_&sysdate..xls;

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

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 sets from excel input file;

*import entrainment data;

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

*import impingement data;

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

*import combined entrainment and impingement data;

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

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

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

*generate a combined entrainment dataset for manufacturers and generators;

data bnb_ent_1;

length option_name $20;

format option_name $20.;

set bnb_ent;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

benefit_value_regional_mn=benefit_value_regional;

Page 1
data_combine_03.21.11.txt

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then benefit_value_regional_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

benefit_value_regional_t=benefit_value_regional;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then benefit_value_regional_t=0;

drop baseline_value benefit_value regional_reduction ccr_fraction;

RUN;

data bnb_ent_mn;

set bnb_ent_1;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then delete;

drop benefit_value_regional_t benefit_value_regional;

if option_name="option.mn1" then option_name="option.tmn1";

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

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

if option_name="option.mn4" then option_name="option.tmn4";

RUN;

data bnb_ent_t;

set bnb_ent_1;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then delete;

drop benefit_value_regional_mn benefit_value_regional;

if option_name="option.t1" then option_name="option.tmn1";

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

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

if option_name="option.t4" then option_name="option.tmn4";

RUN;

Proc sort data=bnb_ent_mn out=bnb_ent_mn;

by option_name region species_econ metric comrec_type;

RUN;

Proc sort data=bnb_ent_t out=bnb_ent_t;

by option_name region species_econ metric comrec_type;

RUN;

data bnb_ent_tot;

merge bnb_ent_mn bnb_ent_t;

by option_name region species_econ metric comrec_type;

RUN;

data bnb_ent_2;

set bnb_ent_tot;

benefit_value_regional= benefit_value_regional_mn +

benefit_value_regional_t;

drop benefit_value_regional_mn benefit_value_regional_t;

RUN;

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

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

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

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

*generate a combined impingement dataset for manufacturers and generators;

data bnb_imp_1;

length option_name $20;

format option_name $20.;

set bnb_imp;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

benefit_value_regional_mn=benefit_value_regional;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then benefit_value_regional_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

benefit_value_regional_t=benefit_value_regional;

Page 2
data_combine_03.21.11.txt

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then benefit_value_regional_t=0;

drop baseline_value benefit_value regional_reduction ccr_fraction f14;

RUN;

data bnb_imp_mn;

set bnb_imp_1;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then delete;

drop benefit_value_regional_t benefit_value_regional;

if option_name="option.mn1" then option_name="option.tmn1";

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

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

if option_name="option.mn4" then option_name="option.tmn4";

RUN;

data bnb_imp_t;

set bnb_imp_1;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then delete;

drop benefit_value_regional_mn benefit_value_regional;

if option_name="option.t1" then option_name="option.tmn1";

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

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

if option_name="option.t4" then option_name="option.tmn4";

RUN;

Proc sort data=bnb_imp_mn out=bnb_imp_mn;

by option_name region species_econ metric comrec_type;

RUN;

Proc sort data=bnb_imp_t out=bnb_imp_t;

by option_name region species_econ metric comrec_type;

RUN;

data bnb_imp_tot;

merge bnb_imp_mn bnb_imp_t;

by option_name region species_econ metric comrec_type;

RUN;

data bnb_imp_2;

set bnb_imp_tot;

benefit_value_regional= benefit_value_regional_mn +

benefit_value_regional_t;

drop benefit_value_regional_mn benefit_value_regional_t;

RUN;

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

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

*generate a combined IM&E dataset for manufacturers and generators;

data bnb_ie_1;

length option_name $20;

format option_name $20.;

set bnb_ie;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

benefit_value_regional_mn=benefit_value_regional;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then benefit_value_regional_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

benefit_value_regional_t=benefit_value_regional;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then benefit_value_regional_t=0;

drop baseline_value benefit_value regional_reduction ccr_fraction;

RUN;

data bnb_ie_mn;

set bnb_ie_1;

if option_name="option.t1" | option_name="option.t2" |

Page 3
data_combine_03.21.11.txt

option_name="option.t3" | option_name="option.t4" then delete;

drop benefit_value_regional_t benefit_value_regional;

if option_name="option.mn1" then option_name="option.tmn1";

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

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

if option_name="option.mn4" then option_name="option.tmn4";

RUN;

data bnb_ie_t;

set bnb_ie_1;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then delete;

drop benefit_value_regional_mn benefit_value_regional;

if option_name="option.t1" then option_name="option.tmn1";

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

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

if option_name="option.t4" then option_name="option.tmn4";

RUN;

Proc sort data=bnb_ie_mn out=bnb_ie_mn;

by option_name region species_econ metric comrec_type;

RUN;

Proc sort data=bnb_ie_t out=bnb_ie_t;

by option_name region species_econ metric comrec_type;

RUN;

data bnb_ie_tot;

merge bnb_ie_mn bnb_ie_t;

by option_name region species_econ metric comrec_type;

RUN;

data bnb_ie_2;

set bnb_ie_tot;

benefit_value_regional= benefit_value_regional_mn +

benefit_value_regional_t;

drop benefit_value_regional_mn benefit_value_regional_t;

RUN;

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

***********

*make changes to the reductions_detail sheet to calculate total ccr_fraction for

manufacturers and

generators combined;

*START BY RE-IMPORTING THE SEPARATE MN and GN VALUES;

*import entrainment data;

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

*import impingement data;

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

*import combined entrainment and impingement data;

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

*import the reductions detail/ccr.fraction sheet;

%import(&inloc.&input,reductions_net,reductions_detail);

data bnb_entx;

set bnb_ent;

RUN;

data bnb_impx;

set bnb_imp;

RUN;

data bnb_iex;

set bnb_ie;

RUN;

Data reductions_detail_1;

Page 4
data_combine_03.21.11.txt

set reductions_detail;

drop regional_reduction_imp regional_reduction_ent ccr_fraction_ent;

RUN;

Proc sort data=reductions_detail_1;

by option_name region;

RUN;

Data bnb_imp_x1;

length benefit_value_regional_imp $20;

format benefit_value_regional_imp $20.;

set bnb_impx;

if species_econ="zz.all.species";

if region ~="zz.allregions";

if comrec_type="all.types";

if metric="ae1";

benefit_value_regional_imp=benefit_value_regional;

drop baseline_value benefit_value baseline_value_regional regional_reduction

ccr_fraction benefit_value_regional;

RUN;

Proc sort data=bnb_imp_x1 out=bnb_imp_x1;

by option_name region;

RUN;

Data bnb_ie_x1;

set bnb_iex;

if species_econ="zz.all.species";

if region ~="zz.allregions";

if comrec_type="all.types";

if metric="ae1";

drop baseline_value benefit_value baseline_value_regional regional_reduction

ccr_fraction;

RUN;

Proc sort data=bnb_ie_x1 out=bnb_ie_x1;

by option_name region;

RUN;

*this calculates separate values for mn and gn;

Data reductions_detail_2;

merge reductions_detail_1 bnb_imp_x1 bnb_ie_x1;

by option_name region;

if benefit_value_regional=0 then ccr_fraction_ie=0;

if benefit_value_regional~=0 then

ccr_fraction_ie=(ccr_fraction_imp*benefit_value_regional_imp+(benefit_value_regional

-benefit_value_regional_imp))/benefit_value_regional;

drop value_basis species_econ lossmode metric comrec_type;

RUN;

Data reductions_detail_3;

*length ccr_fraction_imp_mn $30;

*format ccr_fraction_imp_mn $30.;

*length ccr_fraction_imp_t $30;

*format ccr_fraction_imp_t $30.;

*length baseline_value_regional_imp_mn $30;

*format baseline_value_regional_imp_mn $30.;

*length baseline_value_regional_imp_t $30;

*format baseline_value_regional_imp_t $30.;

*length baseline_value_regional_imp_mn $30;

*format baseline_value_regional_imp_mn $30.;

*length baseline_value_regional_imp_t $30;

*format baseline_value_regional_imp_t $30.;

*length baseline_value_regional_mn $30;

*format baseline_value_regional_mn $30.;

Page 5
data_combine_03.21.11.txt

*length baseline_value_regional_t $30;

*format baseline_value_regional_t $30.;

*length ccr_fraction_ie_mn $30;

*format ccr_fraction_ie_mn $30.;

*length ccr_fraction_ie_t $30;

*format ccr_fraction_ie_t $30.;

set reductions_detail_2;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

ccr_fraction_imp_mn=ccr_fraction_imp;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then ccr_fraction_imp_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

ccr_fraction_imp_t=ccr_fraction_imp;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then ccr_fraction_imp_t=0;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

benefit_value_regional_imp_mn=benefit_value_regional_imp;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

benefit_value_regional_imp_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

benefit_value_regional_imp_t=benefit_value_regional_imp;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

benefit_value_regional_imp_t=0;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

benefit_value_regional_mn=benefit_value_regional;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then benefit_value_regional_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

benefit_value_regional_t=benefit_value_regional;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then benefit_value_regional_t=0;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then

ccr_fraction_ie_mn=ccr_fraction_ie;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then ccr_fraction_ie_mn=0;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then

ccr_fraction_ie_t=ccr_fraction_ie;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then ccr_fraction_ie_t=0;

if ccr_fraction_ie_mn <.0001 then ccr_fraction_ie_mn=0;

if ccr_fraction_ie_t <.0001 then ccr_fraction_ie_t=0;

drop ccr_fraction_imp benefit_value_regional_imp benefit_value_regional

ccr_fraction_ie;

RUN;

Page 6
data_combine_03.21.11.txt

Data reductions_detail_3mn;

length option_name $20;

format option_name $20.;

set reductions_detail_3;

if option_name="option.t1" | option_name="option.t2" |

option_name="option.t3" | option_name="option.t4" then delete;

drop ccr_fraction_imp_t benefit_value_regional_imp_t

benefit_value_regional_t ccr_fraction_ie_t;

if option_name="option.mn1" then option_name="option.tmn1";

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

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

if option_name="option.mn4" then

option_name="option.tmn4";

RUN;

Proc Sort data=reductions_detail_3mn out=reductions_detail_3mn;

by region option_name;

RUN;

Data reductions_detail_3t;

length option_name $20;

format option_name $20.;

set reductions_detail_3;

if option_name="option.mn1" | option_name="option.mn2" |

option_name="option.mn3" | option_name="option.mn4" then delete;

drop ccr_fraction_imp_mn benefit_value_regional_imp_mn

benefit_value_regional_mn ccr_fraction_ie_mn;

if option_name="option.t1" then option_name="option.tmn1";

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

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

if option_name="option.t4" then

option_name="option.tmn4";

RUN;

Proc Sort data=reductions_detail_3t out=reductions_detail_3t;

by region option_name;

RUN;

Data reductions_detail_4;

merge reductions_detail_3mn reductions_detail_3t;

by region option_name;

RUN;

data reductions_detail_5;

set reductions_detail_4;

ccr_fraction_imp= (ccr_fraction_imp_mn*benefit_value_regional_imp_mn +

ccr_fraction_imp_t*benefit_value_regional_imp_t)/(benefit_value_regional_imp_mn +

benefit_value_regional_imp_t);

ccr_fraction_ie= (ccr_fraction_ie_mn*benefit_value_regional_mn +

ccr_fraction_ie_t*benefit_value_regional_t)/(benefit_value_regional_mn +

benefit_value_regional_t);

RUN;

data reductions_detail_6 (keep= option_name region ccr_fraction_imp

ccr_fraction_ie);

set reductions_detail_5;

drop ccr_fraction_ie;

RUN;

Proc Sort data=reductions_detail_6 out=reductions_detail_6;

by option_name region;

RUN;

Page 7
data_combine_03.21.11.txt

*export the revised entrainment dataset;

%export(bnb_ent_2,"&outloc.&output",bnb_ent);

*export the revised impingement dataset;

%export(bnb_imp_2,"&outloc.&output",bnb_imp);

*export the revised IM&E dataset;

%export(bnb_ie_2,"&outloc.&output",bnb_ie);

*export the reductions detail sheet with the impingement fraction numbers;

%export(reductions_detail_6,"&outloc.&output",reductions_detail);

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

Page 8