/* SAMPLE SAS PROGRAM "SEDS95.SAS" */

* This program reads SEDS and SEPEDS data from the CISER Data Archive ;
* CISER Archive Files ;

filename in3 'U:\ArchiveData\econ\063\sedsb94.dat' lrecl= 200;
filename in4 'U:\ArchiveData\econ\062\seprd94.dat' lrecl= 200;
filename in5 'U:\ArchiveData\econ\062\seprv94.dat' lrecl =200; 

libname ssd 'U:\Users\your_netid\';

options ls = 79;

*  For the Residential Sector                                 ;
*  48 States and DC  ;
*  Data on Energy Quantity by Source and Consumption Sector          ;
*  Source: US DOE, State Energy Data System, Consumption Estimates   ;
*  for States, 1970-1994                                             ;

* SEDS data for residential sector;

DATA sedsres;
  INFILE IN3 dlm=',';
  input #1 state $ VARNAME $ d v 
        #2 state $ VARNAME $ d v YR1970-YR1979
        #3 state $ VARNAME $ d v YR1980-YR1989
        #4 state $ VARNAME $ d v YR1990-YR1994;

* include 48 continental states and D.C. ;
data sedsres;
set sedsres(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data sedsres;
set sedsres(where=(varname in ('NGRCB','PARCB','ESRCB')));
data sedsres;
set sedsres;
 where (state ne 'AK' and state ne 'HI' and state ne 'US');

PROC SORT data=sedsres;
    BY state;

* transpose data to yearly format ;
PROC TRANSPOSE DATA=sedsres OUT=seds
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;

PROC SORT data=seds; BY YEAR1 state;

*  Data on Energy Price and Expenditures                             ;

*  Source: US DOE, State Energy Price and Expenditure Data System,   ;

*  1970-1994                          ;

* SEPEDS data for residential sector;
DATA seprdres;
  INFILE IN4 dlm=',';
  input #1 state $ VARNAME $ d v YR1970-YR1979
        #2 state $ VARNAME $ d v YR1980-YR1989
        #3 state $ VARNAME $ d v YR1990-YR1994;
       * include 48 continental states and D.C. ;
data seprdres;
set seprdres(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data seprdres;
set seprdres(where=(varname in ('NGRCD','PARCD','ESRCD')));
data seprdres;
set seprdres; 
 where (state ne 'AK' and state ne 'HI' and state ne 'US');
PROC SORT; BY state;

* transpose data to yearly format;
PROC TRANSPOSE DATA=seprdres   OUT=SEPRD
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;

PROC SORT;
  BY YEAR1 state;

DATA seprvres;
  INFILE IN5 dlm=',';
  input #1 state $ VARNAME $ d v YR1970-YR1979
        #2 state $ VARNAME $ d v YR1980-YR1989
        #3 state $ VARNAME $ d v YR1990-YR1994;
       * include 48 continental states and D.C. ;
data seprvres;
set seprvres(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data seprvres;
set seprvres(where=(varname in ('NGRCV','PARCV','ESRCV')));
data seprvres;
set seprvres; 
 where (state ne 'AK' and state ne 'HI' and state ne 'US');
PROC SORT; BY state;

* transpose data to yearly format;
PROC TRANSPOSE DATA=seprvres   OUT=SEPRV
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;
  PROC SORT;
  BY YEAR1 state;


* Merge  working files in order to create sepeds Data File ;
DATA sepeds;
merge seprd seprv;
by year1 state;
proc sort; by year1 state;

* Merge  working files in order to create seds/sepeds Data File ;
* for the Residential Sector  ;
DATA ssd.res48;
  MERGE SEDS SEPEDS ssd.sa5;
  BY YEAR1 state;
year2 = substr(YEAR1, 3, 4);
year = input (year2, 4.0);
drop year1 year2 MANEAR1 COMEARN;

label
ESRCB = 'Elec Consumption tril Btu'
ESRCD = 'Elec Price $/mil Btu'
ESRCV = 'Elec Expenditures mil $'
NGRCB = 'Nat Gas Consumption tril Btu'
NGRCD = 'Nat Gas Price $/mil Btu'
NGRCV = 'Nat Gas Expenditures mil $'
PARCB = 'Petoleum Consumption tril Btu'
PARCD = 'Petroleum Price $/mil Btu'
PARCV = 'Petroleum Expenditures mil $'
PNF = 'Price Index for Non-Fuel Goods'
STATE = 'Postal Code'
YEAR = 'year';

rename
ESRCB = qel
ESRCD = pel
NGRCB =  qng
NGRCD = png
PARCB = qol
PARCD = pol;

proc datasets library = work;
  delete sedsres seprvres seprdres seprd seprv seds sepeds;


*  For the Commercial Sector                                 ;
*  Data on Energy Quantity by Source and Consumption Sector          ;
*  Source: US DOE, State Energy Data System, Consumption Estimates   ;
*  for States, 1970-1994                                             ;

* SEDS data forcommercial sector;

DATA sedscom;
  INFILE IN3 dlm=',';
  input #1 state $ VARNAME $ d v 
        #2 state $ VARNAME $ d v YR1970-YR1979
        #3 state $ VARNAME $ d v YR1980-YR1989
        #4 state $ VARNAME $ d v YR1990-YR1994;

* include 48 continental states and D.C. ;
data sedscom;
set sedscom(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data sedscom;
set sedscom(where=(varname in ('NGCCB','PACCB','ESCCB','CLCCB')));
data sedscom;
set sedscom;
 where (state ne 'AK' and state ne 'HI' and state ne 'US');

PROC SORT data=sedscom;
    BY state;

* transpose data to yearly format ;
PROC TRANSPOSE DATA=sedscom OUT=seds
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;

PROC SORT data=seds; BY YEAR1 state;

*  Data on Energy Price and Expenditures                             ;

*  Source: US DOE, State Energy Price and Expenditure Data System,   ;

*  1970-1994                          ;

* SEPEDS data for residential sector;
DATA seprdcom;
  INFILE IN4 dlm=',';
  input #1 state $ VARNAME $ d v YR1970-YR1979
        #2 state $ VARNAME $ d v YR1980-YR1989
        #3 state $ VARNAME $ d v YR1990-YR1994;
       * include 48 continental states and D.C. ;
data seprdcom;
set seprdcom(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data seprdcom;
set seprdcom(where=(varname in ('NGCCD','PACCD','ESCCD','CLCCD')));
data seprdcom;
set seprdcom; 
 where (state ne 'AK' and state ne 'HI' and state ne 'US');
PROC SORT; BY state;

* transpose data to yearly format;
PROC TRANSPOSE DATA=seprdcom   OUT=SEPRD
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;

PROC SORT;
  BY YEAR1 state;

DATA seprvcom;
  INFILE IN5 dlm=',';
  input #1 state $ VARNAME $ d v YR1970-YR1979
        #2 state $ VARNAME $ d v YR1980-YR1989
        #3 state $ VARNAME $ d v YR1990-YR1994;
       * include 48 continental states and D.C. ;
data seprvcom;
set seprvcom(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data seprvcom;
set seprvcom(where=(varname in ('NGCCV','PACCV','ESCCV','CLCCV')));
data seprvcom;
set seprvcom; 
 where (state ne 'AK' and state ne 'HI' and state ne 'US');
PROC SORT; BY state;

* transpose data to yearly format;
PROC TRANSPOSE DATA=seprvcom   OUT=SEPRV
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;
  PROC SORT;
  BY YEAR1 state;


* Merge  working files in order to create sepeds Data File ;
DATA sepeds;
merge seprd seprv;
by year1 state;
proc sort; by year1 state;


* Merge working files in order to create seds/sepeds Data File ;
* for the Commercial Sector                                          ;


DATA ssd.comm48;
  MERGE SEDS SEPEDS ssd.sa5 ssd.sa25 ssd.gsp;
  BY YEAR1 state;
year2 = substr(year1, 3, 4);
year = input (year2, 4.0);
drop year1 year2 TINCOME1 PINCOME1 MANEAR1 MANEMP1 TEMP1 MANUCA1;

label
CLCCB = 'Coal Consumption tril Btu'
CLCCD = 'Coal Price $/mil Btu'
CLCCV = 'Coal Expenditures mil $'
ESCCB = 'Elec Consumption tril Btu'
ESCCD = 'Elec Price $/mil Btu'
ESCCV = 'Elec Expenditures mil $'
NGCCB = 'Nat Gas Consumption tril Btu'
NGCCD = 'Nat Gas Price $/mil Btu'
NGCCV = 'Nat Gas Expenditures mil $'
PACCB = 'Petoleum Consumption tril Btu'
PACCD = 'Petroleum Price $/mil Btu'
PACCV = 'Petroleum Expenditures mil $'
STATE = 'Postal Code'
YEAR = 'Year'
POP = 'Population in thousands';

rename
CLCCB = qcl
CLCCD = pcl
ESCCB = qel
ESCCD = pel
NGCCB = qng
NGCCD = png
PACCB = qol
PACCD = pol;

proc datasets library=work;
  delete sedscom seprdcom sprvres seds sepeds;


*  For the Industrial Sector                                 ;
*  48 States and DC  ;
* SEDS data for industrial sector;

DATA sedsind;
  INFILE IN3 dlm=',';
  input #1 state $ VARNAME $ d v 
        #2 state $ VARNAME $ d v YR1970-YR1979
        #3 state $ VARNAME $ d v YR1980-YR1989
        #4 state $ VARNAME $ d v YR1990-YR1994;

* include 48 continental states and D.C. ;
data sedsind;
set sedsind(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data sedsind;
set sedsind(where=(varname in ('NGICB','PAICB','ESICB','CLICB')));
data sedsind;
set sedsind;
 where (state ne 'AK' and state ne 'HI' and state ne 'US');

PROC SORT data=sedsind;
    BY state;

* transpose data to yearly format ;
PROC TRANSPOSE DATA=sedsind OUT=seds
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;

PROC SORT data=seds; BY YEAR1 state;

*  Data on Energy Price and Expenditures                             ;

*  Source: US DOE, State Energy Price and Expenditure Data System,   ;

*  1970-1994                          ;

* SEPEDS data for industrial sector;
DATA seprdind;
  INFILE IN4 dlm=',';
  input #1 state $ VARNAME $ d v YR1970-YR1979
        #2 state $ VARNAME $ d v YR1980-YR1989
        #3 state $ VARNAME $ d v YR1990-YR1994;
       * include 48 continental states and D.C. ;
data seprdind;
set seprdind(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data seprdind;
set seprdind(where=(varname in ('NGICD','PAICD','ESICD','CLICD')));
data seprdind;
set seprdind; 
 where (state ne 'AK' and state ne 'HI' and state ne 'US');
PROC SORT; BY state;

* transpose data to yearly format;
PROC TRANSPOSE DATA=seprdind   OUT=SEPRD
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;

PROC SORT;
  BY YEAR1 state;

DATA seprvind;
  INFILE IN5 dlm=',';
  input #1 state $ VARNAME $ d v YR1970-YR1979
        #2 state $ VARNAME $ d v YR1980-YR1989
        #3 state $ VARNAME $ d v YR1990-YR1994;
       * include 48 continental states and D.C. ;
data seprvind;
set seprvind(drop=d v);
state=substr(state,2,2);
varname=substr(varname,2,5);
data seprvind;
set seprvind(where=(varname in ('NGICV','PAICV','ESICV','CLICV')));
data seprvind;
set seprvind; 
 where (state ne 'AK' and state ne 'HI' and state ne 'US');
PROC SORT; BY state;

* transpose data to yearly format;
PROC TRANSPOSE DATA=seprvind   OUT=SEPRV
  NAME=YEAR1;
  BY state;
  ID VARNAME;
  VAR YR1970-YR1994;
  PROC SORT;
  BY YEAR1 state;

* Merge  working files in order to create sepeds Data File ;
DATA sepeds;
merge seprd seprv;
by year1 state;
proc sort; by year1 state;

* Merge working files in order to create seds/sepeds Data File ;
* for the Industial Sector                                          ;

DATA ssd.ind48;
  MERGE SEDS SEPEDS ssd.sa5 ssd.sa25 ssd.gsp;
  BY YEAR1 state;
year2 = substr(year1, 3, 4);
year = input (year2, 4.0);
drop year1 year2 TINCOME1 PINCOME1 COMEARN COMEMP TEMP1 COMMCA;


label
CLICB = 'Coal Consumption tril Btu'
CLICD = 'Coal Price $/mil Btu'
CLICV = 'Coal Expenditures mil $'
ESICB = 'Elec Consumption tril Btu'
ESICD = 'Elec Price $/mil Btu'
ESICV = 'Elec Expenditures mil $'
NGICB = 'Nat Gas Consumption tril Btu'
NGICD = 'Nat Gas Price $/mil Btu'
NGICV = 'Nat Gas Expenditures mil $'
PAICB = 'Petoleum Consumption tril Btu'
PAICD = 'Petroleum Price $/mil Btu'
PAICV = 'Petroleum Expenditures mil $'
STATE = 'Postal Code'
YEAR = 'Year'
POP='Population in thousands';

rename
CLICB = qcl
CLICD = pcl
ESICB = qel
ESICD = pel
NGICB = qng
NGICD = png
PAICB = qol
PAICD = pol;

proc datasets library=work;
  delete sedsind seprdind  seprvind seds sepeds;

run;

quit;