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

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

*IM&E Data Combine_Adjusted for State Regs_01.25.14.sas

*Author: Ryan Stapler, Abt Associates Inc.

*Created: 8/13/2010

*Updated: 1/25/2014;

*Purpose: This is SAS program that reorganizes the IM&E data to combine

manufacturers and electric generating facilities in order to generate total IM&E

(i.e., manufacturers and generators combined).

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

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

;

*SPECIFY INPUT AND OUTPUT

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

*file locations;

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

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

Estimation\Final_01.25.14\data_edits_adjusted_for_state_regs\;

%let inloc=&mainloc.Inputs\;

%let outloc=&mainloc.Outputs\;

libname outloc "&outloc";

*input files;

%let input=IE_input_Jan.25.2014_adjusted_for_state_regs.xlsx;

*output files;

%let output=IE_outputs_&sysdate._adjusted_for_state_regs.xls;

*DEFINE MACROS FOR IMPORTING AND EXPORTING

DATA**********************************;

*macro to import 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;

*macro to export 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 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" then benefit_value_regional_mn=benefit_value_regional;

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

Page 1
option_name="option.t3" then benefit_value_regional_mn=0;

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

option_name="option.t3" then benefit_value_regional_t=benefit_value_regional;

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

option_name="option.mn3" 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" 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";

RUN;

data bnb_ent_t;

set bnb_ent_1;

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

option_name="option.mn3" 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";

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" then benefit_value_regional_mn=benefit_value_regional;

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

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

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

option_name="option.t3" then benefit_value_regional_t=benefit_value_regional;

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

option_name="option.mn3" 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" 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";

RUN;

data bnb_imp_t;

set bnb_imp_1;

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

option_name="option.mn3" 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";

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" then benefit_value_regional_mn=benefit_value_regional;

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

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

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

option_name="option.t3" then benefit_value_regional_t=benefit_value_regional;

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

option_name="option.mn3" 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" |

option_name="option.t3" 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";

RUN;

data bnb_ie_t;

set bnb_ie_1;

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

option_name="option.mn3" 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";

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;

Page 3
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 MANUFACTURER AND GENERATOR

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;

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 manufacturers and generators;

Data reductions_detail_2;

merge reductions_detail_1 bnb_imp_x1 bnb_ie_x1;

by option_name region;

Page 4
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_regio

nal-benefit_value_regional_imp))/benefit_value_regional;

drop value_basis species_econ lossmode metric comrec_type;

RUN;

Data reductions_detail_3;

set reductions_detail_2;

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

option_name="option.mn3" then ccr_fraction_imp_mn=ccr_fraction_imp;

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

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

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

option_name="option.t3" then ccr_fraction_imp_t=ccr_fraction_imp;

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

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

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

option_name="option.mn3" then

benefit_value_regional_imp_mn=benefit_value_regional_imp;

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

option_name="option.t3" then benefit_value_regional_imp_mn=0;

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

option_name="option.t3" then

benefit_value_regional_imp_t=benefit_value_regional_imp;

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

option_name="option.mn3" then benefit_value_regional_imp_t=0;

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

option_name="option.mn3" then benefit_value_regional_mn=benefit_value_regional;

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

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

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

option_name="option.t3" then benefit_value_regional_t=benefit_value_regional;

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

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

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

option_name="option.mn3" then ccr_fraction_ie_mn=ccr_fraction_ie;

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

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

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

option_name="option.t3" then ccr_fraction_ie_t=ccr_fraction_ie;

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

option_name="option.mn3" 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;

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

Page 5
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" 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";

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;

*set ccr_fraction_imp to 1 for all options & regions because it is no longer

relevant in the model code;

Data reductions_detail_7;

set reductions_detail_6;

ccr_fraction_imp=1;

RUN;

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

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

Page 6