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 1data_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 2data_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 3data_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 4data_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 5data_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 6data_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 7data_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