Cost PS Data.01.24.14_v1.sas

*************************Record for the Final Section 316(b) Existing Facilities Rule***************************** *originally created 06.16.08;

* Inputs: CostData_ForWeights_01.24.14.xlsx;

* Outputs: Multiple Weights &sysdate..xls ;

* Description: creates post-stratified weights based on parameters of interest ; *select drive; %let d=h; *define files location; %let location=&d.:\ERD\TYCHE Contract\Assignments\WA 0-05 (316(b))\Task 6-Provide Technical Support for Rulemaking Activities\Weights Development\SAS\; *define inputs; %let in1=&location.CostData_ForWeights_01.24.14.xlsx; /*SASIN_All; SASIN_P4 ; SASIN_P3_20_50; SASIN_P3_2_20*/ %let sheet=SASIN_All; *define outputs; %let out=&location.Multiple Weights &sysdate..xlsx; *define libname; libname out "&location."; *this is a macro for importing data from excel into sas; %macro import(infile,insheet,out); PROC IMPORT OUT= WORK.&out

DATAFILE= "&infile" DBMS=EXCEL REPLACE; SHEET="&insheet.$"; GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %mend; *this is a macro for exporting data to excel from sas; %macro export(datafile,outfile,sheet); PROC EXPORT DATA= &datafile

OUTFILE= &outfile DBMS=EXCEL replace; SHEET=&sheet; RUN; %mend; *Import the data; %import(&in1.,&sheet.,d1); *drop facilities that are retired or have recirculation technology; Page 1Cost PS Data.01.24.14_v1.sas

*group 2 (group<>2: 656 obs to 16 obs - not used; *group 3 (group<>3: 656 obs to 52 obs - not used; *group 4 (group<>4: 656 obs to 4 obs ; *group 5 (group<>5: 656 obs to 41 obs - not used; *group 6 (group<>6: 656 obs to 12 obs ; *group 7 (group<>7: 656 obs to 382 obs - not used; data d2; set d1; if group ne 6 | anzd=0 | recirc=1 then delete ;

dq = input(dqf,1.) ; drop dqf ; c = cap*1 ; drop cap ; run;

*this macro creates post-stratified weights based on the selected parameter control variable, e.g., number of facilities, total

capacity; %macro poststrat(cv,ind,outd,by); *drop uneeded vars; data t1; set &ind. (keep=MPLID NERC &by. f c dq WGT);

var=&cv.; *if DQWGT~=. then dq=1; run; *get weights that satisfy control totals; proc sql;

create table t2 as select

mplid as id, sum(WGT*&cv.)/sum(dq*&cv.)*dq as WGT2_&cv. /*weight that will give us the control totals*/

from t1 group by &by.

order by id; quit; *get summary stats on weights ; proc univariate data=t2 noprint;

output out=t4 var=WGT2_&cv._V mean=WGT2_&cv._M PCTLPRE=WGT2_&cv._ PCTLPTS=0 5 25 50 75 95 100 ;

var WGT2_&cv. ; run; *add summary stats to weights data; data out.weight_&cv.;

set t2; if _n_=1 then set t4;

if WGT2_&cv.=. then delete; run; *compare facility totals with control totals by NERC region; proc sql;

create table t5 as select

&by., WGT as facC,wgt*c as capC,wgt*f as floC, sum(WGT*&cv.)/sum(dq*&cv.)*dq as fac2,

sum(WGT*&cv.)/sum(dq*&cv.)*c*dq as cap2, Page 2Cost PS Data.01.24.14_v1.sas sum(WGT*&cv.)/sum(dq*&cv.)*f*dq as flo2

from t1 group by &by.

order by &by.; create table compare_&cv. as select

NERC, sum(facC) as facC, sum(capC) as capC, sum(floC) as floC, sum(fac2) as fac2, sum(cap2) as cap2, sum(flo2) as flo2 from t5 group by NERC

order by NERC; quit; *export data; %export(out.weight_&cv.,"&out.",weight_&cv.); %export(compare_&cv.,"&out.",compare_&cv.); %mend; *post-stratify based on facility count; %poststrat(1,d2,fac,NERC); *post-stratify based on steam generating capacity; %poststrat(c,d2,cap,NERC); *post-stratify based on design flow; %poststrat(f,d2,flo,NERC);
PROC DATASETS LIBRARY=work MEMTYPE=DATA KILL ; QUIT ;

******************************************Cost PS Data.01.24.14_v1.sas******************************************

Page 3