Application of SAS Software at NSPI presented by Voytek Grus Pricing Specialist, Marketing Dept. for

1
Application of SAS Software at NSPIpresented by
Voytek GrusPricing Specialist, Marketing
Dept.for

• SAS user group, Halifax
• March 11, 2005

2
NSPI in brief

• Vertically integrated Power Company under the
  allowable rate of return regulation
• 450,000 customers, 1 billion revenues, 1700
  employees
• Costs of service are subject to regulatory
  scrutiny.
• Need regulatory approval to set prices
• Drivers behind information needs
• Operational and strategic decision making.
• Regulatory requirements.
• Industry trends
• deregulation of the industry
• Complex pricing arrangements
• Energy conservation

3
SAS application at NSPI Purpose and resources

• Purpose
• To meet analytical information needs of marketing
  and regulatory departments through
• supporting to cost of service studies.
• testing bill impacts of price changes
• To meet marketing data mining needs
• Resources
• 2 licenses but only one active user.
• Products used Version 8.0, Base
  SAS/ACCESS/ASSIST/FSP/GRAPH/STAT
• 1 desktop machine, double pentium processor 864
  MHz each, 1 Gigabyte of RAM, 66 GIGs of hard
  drive memory.
• Pricing specialist spends about 20 of his time
  working in SAS to provide information.

4
Philosophy behind SAS applications

• Meet the objective of satisfying companys
  information needs in a timely and cost-efficient
  manner through the means of
• A right balance between SAS and Excel
  applications.
• Permanent storage of some operational data
  records in SAS datasets
• Extract value from a careful database design
• Disciplined approach to the code development.

5
Philosophy behind SAS applications tap into a
symbiotic relationship between SAS and Excel

• Will not shy from moving back and forth between
  SAS and Excel if it speeds up the process using
  these two procedures
• PROC IMPORT OUT WORK.index3
• DATAFILE "K\VJG\Projects\Unmetered\M
  eteredTrafficLights.xls"
• DBMSEXCEL2000 REPLACE
• RANGE"MetTrafficL"
• GETNAMESYES
• RUN
• PROC EXPORT DATA WORK.test
• OUTFILE "K\VJG\Rate Case 2005\Bill
  Impacts\cmesmall.xls"
• DBMSEXCEL2000 REPLACE
• RUN

6
Philosophy behind SAS applications permanent
databases

• Maintain several databases
• Monthly billing records
• Customer consumption records (17 millions)
• Has a unique premise index (for the rate design
  studies) maintained by the billing dept.
• Customer information records (1 million)
• Hourly consumption data for a few hundred
  customers on load research sample (6 million)
• Various hourly costs production data (hundred of
  thousand records)

7
Database maintenance reading-in text file
records can be a challenge for beginners
Example of test data structure read in the data
step

• Text files read in in the SAS data step
• filename FileList source
• infile FileList

8
Database maintenance useful code for reading-in
text files

• let sourcedirC\Load Research\'
• let sourceC\Load Research\dir.txt'
• data example
• filename FileList source
• infile FileList
• length filename_prn 12 PRNname 89
• input filename_prn 40-51
• PRNname sourcedir filename_prn
• infile PRNname filevar PRNname end done
  firstobs 2
• do until ( done )
• input lrid 2-15 date 18-23 hour 25-26 min
  27-28 kwhs_lr 40-50
• keep filename_prn lrid hourlydate
  kwhs_lr
• output
• end
• run

Find these statements very useful
Will use expand procedure to deal with missing
observations.

• proc expand datansr outnsr fromhour tohour
• convert nsr tsr exports / methodjoin
• id hourlydate
• run

9
Database maintenance update, index and
where statements

• data mf.static
• update mf.static static
• by rc premise account name
• run
• proc datasets libmf
• modify static
• index create premise
• index create rpan(rc premise account name)
• run
• data ExtractRecord
• set mf.static
• where premise100
• run

Use update statement to update database by key
indices.
Creates indices for big databases to access
records efficiently
Use where statement to extract a data records
for premise 100
10
Philosophy of SAS application at NSPI invest
time in careful database design.

• Database design is a key for an efficient code
  writing
• The fewer variables the the better
• Example of efficiency gain in aggregation

This design is more preferable
than this
This data structure lends itself to more
efficient processing
proc means datainput sum noprint by voltage
class hour var KWhs output outoutput sum run
data aggregate set input Kwhs_allKWhs_res_dis
t KWhs_res_comm_ ...etc run
11
Philosophy of SAS usage at NSPI modular approach
to SAS code.

• Delegates specific tasks to SAS programs invoked
  by include statement from the master program
• / SECTION I Bottom-Up Calc. Prepare raw load
  shapes by rate and voltage classes /
• include 'C\PROGRAMMS\LSbyRCL1.1 Prepare Accrued
  Classes.sas'
• include 'C\PROGRAMMS\LSbyRCL1.2 Prepare Large
  Customers.sas'
• include 'C\PROGRAMMS\LSbyRCL1.3 Extract
  NSR.sas'
• include 'C\PROGRAMMS\LSbyRCL1.5 Combine and
  scale to oracle rc shapes.sas'
• include 'C\PROGRAMMS\LSbyRCL1.6 Combine rc into
  vc shapes.sas'
• / SECTION II Top-Down Calc. Calibrate line
  losses by Voltage Classes /
• include 'C\PROGRAMMS\LSbyRCL2.1 Calc Line
  losses by vc.sas'
• etc....

12
SAS programming philosophy at NSPI standard
naming convention, use of global parameters.

• Extensive use of global parameters (macros)
• let Juan1dhms(mdy(9,29,2003),0,0,0) / adj for
  the huricane /
• let Juan2dhms(mdy(10,5,2003),23,0,0)
• .
• data nochange Juan_rpl
• set alldata
• if (hourlydate lt Juan1 or hourlydate gt Juan2)
  then output nochange
• if (hourlydate gt Juan1-(Juan2-Juan1)-3600
  and hourlydate lt Juan2-(Juan2-Juan1)) then
  output Juan_rpl
• run

13
SAS applications Cost of Service Studies

• NSPI is mandated to provide cost of service
  studies to the regulator
• Studies determine cost of service to over a dozen
  customer groups such as residential, commercial
  industrial rate classes.
• Need to determine hourly consumption patterns
  (Load Shapes of 8760 hours) by various groups
  before costs can be allocated.
• Simplified Diagram

Econometric Load forecast Projects monthly
sales by rate class. (Excel) (Marketing Dept)
Allocation of Costs to rate classes (Excel) (Regul
atory Dept)
Calculates sample weights using Customer monthly
billing records. ( 5 million recs in SAS
db)) Marketing Dept.
Calculate Hourly Sales patterns for 7 rate
classes (in MV90 db) (Load Research Dept)
Calculate hourly sales, losses, requirements by
rate class to arrive at cost allocation stats
such as coincident and non-coincident peak. (SAS)
Hourly Load Research Data for 700 customers (6
million records in MV90 db) Load Research Dept.
14
SAS application Contribution to Cost of Service
Studies
15
SAS Applications Customer Bill Impacts

• Changes in prices are subject to regulatory
  approval and need to be communicated to NSPI
  Management, the UARB and customers of NSPI.

Rate Design Allocation of cost responsibilities
among individual customers (Marketing Dept. )
Allocation of costs among customer
classes (Regulatory Dept. )
Allocation of cost responsibilities s among
customer classes (Marketing Dept. )
Revenue Deficiency (Finance Dept. )
No
Submit new tariffs
Are bill impacts acceptable to NSPI, UARB,
Customers?
Give Customers access to bill impacts on the
internet
Will test bill impacts of proposed rate
structure on all customers using SAS (Marketing
Dept. )
Yes
16
Bill Impacts an example of final Output for the
UARB
17
Bill Impacts Final Output for NSPI customers
available on the NSPI website in 2002.
Screen 1 enter personal info.
Screen 2 customized bill impact.
18
Customer Bill Impacts programming solutions

• To simulate bills use pointer technique to keep
  the size of work-in-progress data files at a
  minimum
• data M_methodtypescenario /(keeprc premise
  year month simrev)/
• (drop code min_bill ratecode montht yeart pos
  tc
• base_ch demand_on_ch demand_off_ch
  kwh_blk1_ch)
• set dets_typescenario
• include 'C\Programms\Bill Simulation 1.2.1
  Calc Bill.sas'
• pointer((year-ftyear)12month-1)32pos
• set tariffs.M_scenario pointpointer
• if index(bf,'T')0 then tc0else tctoc
• if rc in (2 3) and index(bf,'P')gt 0 then

select when (rc2) pos1
when (rc3) pos2
when (rc4) pos3 when (rc5)
pos4 when (rc6) pos5
when (rc7) pos6
when (rc8) pos7 when (rc9)
pos8 when (rc10) pos9 Etc
19
Customer Bill Impacts programming solutions

• To calculate accrued monthly records will use
  arrays and will split datasets into smaller
  subsets to speed up processing of files which
  otherwise could reach undesirable size of dozens
  of millions of records.
• data accrsort1(dropi) accrsort2(dropi)
• / Rearrange data in preparation for aggregation
  /
• set billed_partitioned
• array kwhsblk16 kwhsblk11-kwhsblk16
• array kwhsblk26 kwhsblk21-kwhsblk26
• etc ...
• do i1 to 6
• yearyear(datexi)
• monthmonth(datexi)
• kwhs_blk1kwhsblk1(i)
• kwhs_blk2kwhsblk2(i)
• etc ...
• if i lt3 then output accrsort1
• else output accrsort2
• end
• run

20
Price Studies Finding a time pattern for pricing
structures.

• Use a by group processing feature of graph and
  regression procedures to efficiently analyze
  data.

21
Price Studies Finding a time pattern for pricing
structures.

• Example of a by group processing feature of a
  Regression procedure
• .
• let regress_byyear month daytype
• proc sort datainput_data outoutput_data
• by regress_by
• run
• proc reg dataoutput_data outestregstats
  tableout
• by regress_by
• m1 model NSR mid_peak1 on_peak1
• m2 model NSR on_peak2
• m3 model mc on_peak2
• run
• data m1_coeff(keepregress_by intercept
  mid_peak1 on_peak1 rename(interceptintercept1
  mid_peak1mp1c on_peak1op1c))
• m2_coeff(keepregress_by intercept
  on_peak2 rename(interceptintercept2
  on_peak2op2c))
• m3_coeff(keepregress_by intercept on_peak2
  rename(interceptintercept3 on_peak2op3c))
• set regstats
• if _type_'PARMS' and _model_'M1' then
  output m1_coeff
• if _type_'PARMS' and _model_'M2' then
  output m2_coeff

22
Employs SAS graph capabilities to assist Large
Customers in their planning process.