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 1
Cost 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 2
Cost 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