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

********************************************************************************* *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 1option_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 2if 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 3set 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 4if 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 5RUN;

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