NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M5)
      Licensed to CITY UNIVERSITY OF NEW YORK, Site 70080308.
NOTE: This session is executing on the X64_10PRO  platform.



NOTE: Updated analytical products:

      SAS/STAT 14.3
      SAS/ETS 14.3
      SAS/OR 14.3
      SAS/IML 14.3
      SAS/QC 14.3

NOTE: Additional host information:

 X64_10PRO WIN 10.0.22621  Workstation

NOTE: SAS initialization used:
      real time           2.51 seconds
      cpu time            0.99 seconds

NOTE: Unable to open SASUSER.REGSTRY. WORK.REGSTRY will be opened instead.
NOTE: All registry changes will be lost at the end of the session.
WARNING: Unable to copy SASUSER registry to WORK registry. Because of this,
WARNING: you will not see registry customizations during this session.
NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.
NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of this
NOTE: session.
NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.

1    libname dist 'C:\Users\yjia\Dropbox\dist';
NOTE: Libref DIST was successfully assigned as follows:
      Engine:        V9
      Physical Name: C:\Users\yjia\Dropbox\dist
2
3
4    /***county level population data ***/
5    PROC IMPORT OUT= work.pop DATAFILE= "C:\Users\yjia\Dropbox\dist\pop_income_county.xlsx"
6                DBMS=xlsx REPLACE;
7         SHEET="Sheet1";
8         GETNAMES=YES;
9    RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 86373 observations and 8 variables.
NOTE: WORK.POP data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           6.00 seconds
      cpu time            5.82 seconds


10   libname dist 'C:\Users\yjia\Dropbox\dist';
NOTE: Libref DIST was successfully assigned as follows:
      Engine:        V9
      Physical Name: C:\Users\yjia\Dropbox\dist
11
12
13   /***county level population data ***/
14   PROC IMPORT OUT= work.pop DATAFILE= "C:\Users\yjia\Dropbox\dist\pop_income_county.xlsx"
15               DBMS=xlsx REPLACE;
16        SHEET="Sheet1";
17        GETNAMES=YES;
18   RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 86373 observations and 8 variables.
NOTE: WORK.POP data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           6.18 seconds
      cpu time            5.87 seconds


19
20   data pop;
21   set pop;
22   fips3 = compress(GeoFIPS,'"');
23   if index(GeoName,',') then county=1;
24   if county=1;
25   if income ~='(NA)';
26
27   run;

NOTE: There were 86373 observations read from the data set WORK.POP.
NOTE: The data set WORK.POP has 84011 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.04 seconds


28
29   /**create change in income***/
30   proc sql;
31    create table pop_lag as
32    select a.*, b.income as lag_income
33     from pop a left join pop b
34    on a.GeoFIPS = b.GeoFIPS and a.year = b.year+1;
NOTE: Table WORK.POP_LAG created, with 84011 rows and 11 columns.

35   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.16 seconds
      cpu time            0.06 seconds


36
37
38   data pop2;
39   set pop_lag ;
40   if year>=1995;
41   incomegr = (income-lag_income)/lag_income;
42   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      41:13   41:20   41:32
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      6 at 41:19
NOTE: There were 84011 observations read from the data set WORK.POP_LAG.
NOTE: The data set WORK.POP2 has 77791 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.03 seconds


43
44
45   /**imput emp**/
46   data emp_data;
47   set dist.emp_data_2023;
48   fips = substr(state,1,2) || substr(county,1,3);
49   format fips $8.;
50   year2 = input(year, 12.);
51   drop year;
52   run;

NOTE: There were 80520 observations read from the data set DIST.EMP_DATA_2023.
NOTE: The data set WORK.EMP_DATA has 80520 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds


53
54
55   proc sql;
56    create table rs3 as
57    select a.*, b.incomegr, b.pop
58     from emp_data a left join pop2 b
59    on b.fips1 = a.State and b.fips2 =a.County and b.year = a.year2;
NOTE: Table WORK.RS3 created, with 80520 rows and 8 columns.

60   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.13 seconds
      cpu time            0.12 seconds


61   data rs4;
62   set rs3;
63   if state ~='';
64   run;

NOTE: There were 80520 observations read from the data set WORK.RS3.
NOTE: The data set WORK.RS4 has 80445 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


65
66
67
68
69   /**many missing values due to mismatch in fips1 and county definition for Virgina--> manually
69 !  clean up***/
70
71   PROC IMPORT OUT= work.va_missing DATAFILE= "C:\Users\yjia\Dropbox\dist\va_missing.xlsx"
72               DBMS=xlsx REPLACE;
73        SHEET="Sheet1";
74        GETNAMES=YES;
75   RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 51 observations and 6 variables.
NOTE: WORK.VA_MISSING data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


76
77   data va_missing_match;
78   set rs4;
79   if incomegr = .;
80   if state =51;
81
82   drop incomegr  pop;
83   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      80:4
NOTE: There were 80445 observations read from the data set WORK.RS4.
NOTE: The data set WORK.VA_MISSING_MATCH has 1275 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


84   proc sql;
85    create table va_missing_match2 as
86    select a.*, b.fips_va
87     from va_missing_match a left join va_missing b
88    on b.State = a.State and b.County =a.County;
NOTE: Table WORK.VA_MISSING_MATCH2 created, with 1275 rows and 7 columns.

89   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


90
91
92   proc sql;
93   create table va_missing_match3 as
94   select *, count(state) as count
95   from va_missing_match2
96   group by fips_va, year2;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.VA_MISSING_MATCH3 created, with 1275 rows and 8 columns.

97   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


98
99   proc sql;
100   create table va_missing_match4 as
101   select a.*, b.incomegr, b.pop
102    from va_missing_match3 a left join pop2 b
103   on b.fips1 = a.State and b.fips2 =a.fips_va and b.year = a.year2;
NOTE: Table WORK.VA_MISSING_MATCH4 created, with 1275 rows and 10 columns.

104  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds


105
106  data  va_missing_match5;
107  set  va_missing_match4;
108  pop = pop/count;
109  run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      108:7
NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      108:10
NOTE: There were 1275 observations read from the data set WORK.VA_MISSING_MATCH4.
NOTE: The data set WORK.VA_MISSING_MATCH5 has 1275 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


110
111  proc sql;
112   create table rs4a as
113   select a.*, b.incomegr as incomegr2, b.pop as pop2
114    from rs4 a left join  va_missing_match4 b
115   on b.State = a.State and b.County =a.County and b.year2 = a.year2;
NOTE: Table WORK.RS4A created, with 80445 rows and 10 columns.

116  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.03 seconds


117  data rs4b;
118  set rs4a;
119  if (incomegr = . and incomegr2 ~=.) then incomegr = incomegr2;
120  if (pop = . and pop2 ~=. )then pop = pop2;
121  drop incomegr2 income2 pop2;
122  run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      120:5    120:17
WARNING: The variable income2 in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: There were 80445 observations read from the data set WORK.RS4A.
NOTE: The data set WORK.RS4B has 80445 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds


123
124  data rs5;
125  set rs4b;
126
127  if year2 <=1996 then year_reg = 1990;
128  if year2 >=1996 and year2 <=2005 then year_reg = 2000;
129  if year2 >=2006 and year2 <=2015 then year_reg = 2010;
130  if year2 >2015 then year_reg = 2020;
131  run;

NOTE: There were 80445 observations read from the data set WORK.RS4B.
NOTE: The data set WORK.RS5 has 80445 observations and 9 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds


132  /***import religion data**/
133  PROC IMPORT OUT= work.reg_1990 DATAFILE= "C:\Users\yjia\Dropbox\dist\religion_county.xlsx"
134              DBMS=xlsx REPLACE;
135       SHEET="1990";
136       GETNAMES=YES;
137  RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 3141 observations and 6 variables.
NOTE: WORK.REG_1990 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.19 seconds
      cpu time            0.07 seconds


138
139  PROC IMPORT OUT= work.reg_2000 DATAFILE= "C:\Users\yjia\Dropbox\dist\religion_county.xlsx"
140              DBMS=xlsx REPLACE;
141       SHEET="2000";
142       GETNAMES=YES;
143  RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 3142 observations and 6 variables.
NOTE: WORK.REG_2000 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.20 seconds
      cpu time            0.10 seconds


144
145  PROC IMPORT OUT= work.reg_2010 DATAFILE= "C:\Users\yjia\Dropbox\dist\religion_county.xlsx"
146              DBMS=xlsx REPLACE;
147       SHEET="2010";
148       GETNAMES=YES;
149  RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 3149 observations and 6 variables.
NOTE: WORK.REG_2010 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.21 seconds
      cpu time            0.09 seconds


150  PROC IMPORT OUT= work.reg_2020 DATAFILE= "C:\Users\yjia\Dropbox\dist\religion_county.xlsx"
151              DBMS=xlsx REPLACE;
152       SHEET="2020";
153       GETNAMES=YES;
154  RUN;

NOTE:    Variable Name Change.  County Name -> County_Name
NOTE:    Variable Name Change.  2020 Population -> _2020_Population
NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 3143 observations and 5 variables.
NOTE: WORK.REG_2020 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.17 seconds
      cpu time            0.09 seconds


155  data reg_1990;
156  set reg_1990;
157  keep fips year regrate;
158  run;

NOTE: There were 3141 observations read from the data set WORK.REG_1990.
NOTE: The data set WORK.REG_1990 has 3141 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


159  data reg_2000;
160  set reg_2000;
161  keep fips year regrate;
162  run;

NOTE: There were 3142 observations read from the data set WORK.REG_2000.
NOTE: The data set WORK.REG_2000 has 3142 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


163  data reg_2010;
164  set reg_2010;
165  keep fips year regrate;
166  run;

NOTE: There were 3149 observations read from the data set WORK.REG_2010.
NOTE: The data set WORK.REG_2010 has 3149 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


167  data reg_2020;
168  set reg_2020;
169  keep fips year regrate;
170  run;

NOTE: There were 3143 observations read from the data set WORK.REG_2020.
NOTE: The data set WORK.REG_2020 has 3143 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


171
172  data religion_all_county;
173  set reg_1990 reg_2000 reg_2010 reg_2020;
174  run;

NOTE: There were 3141 observations read from the data set WORK.REG_1990.
NOTE: There were 3142 observations read from the data set WORK.REG_2000.
NOTE: There were 3149 observations read from the data set WORK.REG_2010.
NOTE: There were 3143 observations read from the data set WORK.REG_2020.
NOTE: The data set WORK.RELIGION_ALL_COUNTY has 12575 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


175
176  proc sql;
177   create table rs5_reg as
178   select a.*, b.regrate
179    from rs5 a left join religion_all_county  b
180   on a.fips = b.fips and a.year_reg = b.year;
NOTE: Table WORK.RS5_REG created, with 80445 rows and 10 columns.

181  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.10 seconds
      cpu time            0.07 seconds


182
183
184
185  /***merge district to county**/
186  PROC IMPORT OUT= work.collect_county_court2 DATAFILE=
186! "C:\Users\yjia\Dropbox\dist\collect_county_court2.xlsx"
187              DBMS=xlsx REPLACE;
188       SHEET="collect_county_court2";
189       GETNAMES=YES;
190  RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9
      engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 3221 observations and 20 variables.
NOTE: WORK.COLLECT_COUNTY_COURT2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.21 seconds
      cpu time            0.12 seconds


191
192  proc sql;
193   create table rs6 as
194   select a.*, b.state_final,b.District
195    from rs5_reg a left join collect_county_court2  b
196   on a.fips = b.fips3;
NOTE: Table WORK.RS6 created, with 80445 rows and 12 columns.

197  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.03 seconds


198  data rs7;
199  set rs6;
200  if state_final ~='';
201  pop2 = input(pop,8.);
202  drop pop;
203  run;

NOTE: There were 80445 observations read from the data set WORK.RS6.
NOTE: The data set WORK.RS7 has 80420 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds


204
205
206  /**copute weighted average***/
207  proc sql;
208  create table rs8 as
209  select *, sum(pop2)as total_pop
210  from rs7
211  group by state_final,district, year2;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.RS8 created, with 80420 rows and 13 columns.

212  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.10 seconds
      cpu time            0.07 seconds


213  data rs9;
214  set rs8;
215  wgt = pop2/total_pop;
216  w_incomegr = wgt*incomegr;
217  w_unemprate = wgt*unemp_rate;
218  w_regrate = wgt*regrate;
219  run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1961 at 215:11   1962 at 216:17   1975 at 217:18   2033 at 218:16
NOTE: There were 80420 observations read from the data set WORK.RS8.
NOTE: The data set WORK.RS9 has 80420 observations and 17 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.04 seconds


220  proc sql;
221  create table rs10 as
222  select *, sum(w_incomegr) as ws_incomegr, sum(w_unemprate) as ws_unemprate,sum(w_regrate) as
222! ws_regrate
223  from rs9
224  group by state_final,district, year2;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.RS10 created, with 80420 rows and 20 columns.

225  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      cpu time            0.10 seconds


226  data rs11;
227  set rs10;
228  keep state_final district year2 ws_incomegr ws_unemprate ws_regrate total_pop;
229  ws_incomegr = ws_incomegr*100;
230  ws_regrate = ws_regrate;
231  run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1925 at 229:26
NOTE: There were 80420 observations read from the data set WORK.RS10.
NOTE: The data set WORK.RS11 has 80420 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


232  proc sort data = rs11 out= final_county_control nodupkey;
233  by state_final district year2;
234  quit;

NOTE: There were 80420 observations read from the data set WORK.RS11.
NOTE: 78020 observations with duplicate key values were deleted.
NOTE: The data set WORK.FINAL_COUNTY_CONTROL has 2400 observations and 7 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds


235
236  data dist.final_county_control;
237  set final_county_control;
238  run;

NOTE: There were 2400 observations read from the data set WORK.FINAL_COUNTY_CONTROL.
NOTE: The data set DIST.FINAL_COUNTY_CONTROL has 2400 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


