
. global datapath "...\Data and code for publication_2025JAR"

. 
. cd "$datapath"
...\Data and code for publication_2025JAR


**deposit
. frame reset

. import delimited "...\sod_deposit\ALL_2011.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 98,193 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2011

. 
. import delimited "...\sod_deposit\ALL_2012.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 97,340 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2012

. 
. import delimited "...\sod_deposit\ALL_2013.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 96,339 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2013

. 
. import delimited "...\sod_deposit\ALL_2014.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 94,725 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2014

. 
. import delimited "...\sod_deposit\ALL_2015.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 93,272 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2015

. 
. import delimited "...\sod_deposit\ALL_2016.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 91,834 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2016

. 
. import delimited "...\sod_deposit\ALL_2017.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 89,849 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2017

. 
. import delimited "...\sod_deposit\ALL_2018.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 88,075 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2018

. 
. import delimited "...\sod_deposit\ALL_2019.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 86,392 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2019

. 
. import delimited "...\sod_deposit\ALL_2020.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 84,982 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2020

. 
. import delimited "...\sod_deposit\ALL_2021.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 81,791 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2021

. 
. import delimited "...\sod_deposit\ALL_2022.csv", clear 
(encoding automatically selected: ISO-8859-1)
(79 vars, 79,186 obs)

. *keep year namefull cntynamb stalpbr zipbr depsumbr cntynumb city2br citybr stcntybr stnumbr
. keep year stalpbr zipbr depsumbr

. frame copy default all_2022

. 
. frame change all_2011

. frameappend all_2012

. frameappend all_2013

. frameappend all_2014

. frameappend all_2015

. frameappend all_2016

. frameappend all_2017

. frameappend all_2018

. frameappend all_2019

. frameappend all_2020

. frameappend all_2021

. frameappend all_2022

. 
. replace depsumbr=subinstr(depsumbr,",","",.)
(1,034,452 real changes made)

. destring, replace
year already numeric; no replace
stalpbr: contains nonnumeric characters; no replace
zipbr already numeric; no replace
depsumbr: all characters numeric; replaced as long

. 
. gen zip3 = int(zipbr/100)

. sort zip3 year

. by zip3  year: egen deposit_zip3 = sum(depsumbr)

. gen zip3_num = zip3

. sort zip3 year

. duplicates drop zip3 year, force

Duplicates in terms of zip3 year

(1,071,291 observations deleted)

. // rename year _year
. gen year_deposit = year

. drop year stalpbr zipbr depsumbr zip3_num

. save "deposit_zip3_20112022.dta", replace
(file deposit_zip3_20112022.dta not found)
file deposit_zip3_20112022.dta saved




**Census
. use census.dta, replace

. 
. gen zip5 = substr(name_e,-5,.)

. 
. gen zip3 = substr(zip5, 1, 3)

. 
. gen population = ajwbe001

. drop if population == 0
(321 observations deleted)

. drop if population == .
(0 observations deleted)

. 
. egen population_zip3 = sum(population), by (zip3)

. 
. *income
. corr aj0ee001 aj0em001
(obs=32,456)

             | aj0ee001 aj0em001
-------------+------------------
    aj0ee001 |   1.0000
    aj0em001 |   0.4566   1.0000


. gen _percapitaincome_weighted = aj0ee001*population/population_zip3
(343 missing values generated)

. egen percapitaincome_zip3 = sum(_percapitaincome_weighted), by (zip3)

. 
. // *sort zip3
. // *by zip3: egen _income_zip3 = sum(aj0ee001*population)
. // *gen percapitaincome_b = _income_zip3/ population_zip3  
. 
. gen l_percapitaincome_zip3 =log(percapitaincome_zip3+1)

. 
. 
. *female pct
. gen female = ajwbe026

. egen female_zip3 = sum(female), by (zip3)

. gen female_pct_zip3 = female_zip3/population_zip3

. 
. *sort female_pct
. 
. gen elderlyfemale = ajwbe044 + ajwbe045 + ajwbe046 + ajwbe047 + ajwbe048 + ajwbe049

. gen elderlymale = ajwbe020 + ajwbe021 + ajwbe022 + ajwbe023 + ajwbe024 + ajwbe025

. gen elderly = elderlyfemale + elderlymale

. egen elderly_zip3 = sum(elderly), by (zip3)

. gen elderly_pct_zip3 = elderly_zip3/population_zip3

. 
. gen _median_age = ajwce001*population/population_zip3
(243 missing values generated)

. egen median_age_zip3 = sum(_median_age), by (zip3)

. 
. *education
. gen bachlordegree = ajype022 + ajype023 + ajype024 + ajype025

. // gen _bachlor_pct = bachlordegree/ajwbe001
. // corr _bachlor_pct aj0ee001
. egen _bachlorpopulation_zip3 = sum(ajype001), by (zip3)

. egen bachlordegree_zip3 = sum(bachlordegree), by (zip3)

. gen _bachlordegree_pct_zip3 = bachlordegree_zip3/population_zip3

. gen bachlordegree_pct_zip3 = bachlordegree_zip3/_bachlorpopulation_zip3

. 
. *non-white
. gen nonwhite = ajwne001 - ajwne002

. egen nonwhite_zip3 = sum(nonwhite), by (zip3)

. gen nonwhite_pct_zip3 = nonwhite_zip3/population_zip3

. egen africanamerican1_zip3 = sum(ajwne003), by (zip3)

. gen africanamerican_pct1_zip3 = africanamerican1_zip3/population_zip3

. egen asian1_zip3 = sum(ajwne005), by (zip3)

. gen asian_pct1_zip3 = asian1_zip3/population_zip3

. egen indianalaskanative1_zip3 = sum(ajwne004), by (zip3)

. gen indianalaskanative_pct1_zip3 = indianalaskanative1_zip3/population_zip3

. egen hawaiian1_zip3 = sum(ajwne006), by (zip3)

. gen hawaiian_pct1_zip3 = hawaiian1_zip3/population_zip3

. egen otherrace_zip3 = sum(ajwne007), by (zip3)

. gen otherrace_pct1_zip3 = otherrace_zip3/population_zip3

. 
. 
. egen hispanic_zip3 = sum(ajwwe003), by (zip3)

. gen hispanic_pct_zip3 = hispanic_zip3/population_zip3

. 
. // egen asian1_zip3 = sum(ajwne005), by (zip3)
. // gen asian_pct1_zip3 = asian1_zip3/population_zip3
. //
. // egen indianalaskanative1_zip3 = sum(ajwne004 +ajwne006), by (zip3)
. // gen indianalaskanative_pct1_zip3 = indianalaskanative1_zip3/population_zip3
. //
. // su nonwhite_pct_zip3  africanamerican_pct1_zip3 hispanic_pct_zip3  asian_pct1_zip3 indianalaskanative_pct1_zip3
. // gen _totalraceused_pct = (ajwne002 + ajwne003 + ajwne005 + ajwne004 +ajwne006)/ajwne001
. // su _totalraceused
. // gen _totalbyrace = (ajwne002 + ajwne003 + ajwne005 + ajwne004 +ajwne006 +ajwne007 +ajwne009 +ajwne010)/ajwne001
. // gen other_pct = ajwne007/ajwne001
. // gen nonwhite_pct =nonwhite/ajwne001
. // su nonwhite_pct
. // su other_pct
. // gen combine_pct = (ajwne009 +ajwne010)/ajwne001
. // su combine_pct 
. // gen _allraceused = (ajwne003 + ajwne004 +ajwne006 + ajwne005 + ajwne 007)/(ajwne001 - ajwne002)
. // su _allraceused
. 
. 
. 
. // gen white_pct = ajwne002/ajwne001
. // gen africanamerican_pct = ajwne003/ajwne001
. // gen ameindian_alaska_pct = ajwne004/ajwne001
. // gen asian_pct = ajwne005/ajwne001
. // gen hawaiinative_pct = ajwne006/ajwne001
. // gen other_pct = ajwne007/ajwne001
. // gen tworace_pct = (ajwne009+ajwne010)/ajwne001
. // su white_pct  africanamerican_pct ameindian_alaska_pct asian_pct  hawaiinative_pct other_pct tworace_pct
. 
. // su ajwne002 ajwne003 ajwne005 ajwne004 ajwne006 ajwne007 ajwne009 ajwne010
. 
. 
. keep population_zip3 female_pct_zip3 elderly_pct_zip3 median_age_zip3 _bachlordegree_pct_zip3 bachlordegree_pct_zip3 percapitain
> come_zip3 ///
> l_percapitaincome nonwhite_pct_zip3 zip3 ///
> nonwhite_pct_zip3 africanamerican_pct1_zip3  asian_pct1_zip3 indianalaskanative_pct1_zip3 otherrace_zip3 hawaiian_pct1_zip3 othe
> rrace_pct1_zip3 hispanic_pct_zip3 

. 
. gen zip3_num = real(zip3)

. 
. duplicates drop zip3, force

Duplicates in terms of zip3

(31,910 observations deleted)

. 
. *corr bachlordegree_pct_zip3 percapitaincome_zip3

. save census_zip3_covid.dta, replace
(file census_zip3_covid.dta not found)
file census_zip3_covid.dta saved



. 


. frame reset

. 
. use bls_county_20220601.dta, clear

. capture drop _series_id_last2digit

. gen _series_id_last2digit = substr(series_id,19,2)

. drop if _series_id_last2digit == "05"
(1,351,438 observations deleted)

. drop if _series_id_last2digit == "03"
(1,351,438 observations deleted)

. drop if period == "M13"
(205,932 observations deleted)

. capture drop fips

. gen fips =  real(substr(series_id,6,5))

. 
. /*collapse labor force and unemployment*/
. preserve

. keep if _series_id_last2digit == "06"
(1,248,472 observations deleted)

. gen laborforce =real(value)
(226 missing values generated)

. sort year period series_id

. keep year period laborforce fips

. frame copy default bls_laborforce, replace
(note: frame bls_laborforce not found)

. *save bls_laborforce.dta, replace
. restore

. 
. keep if _series_id_last2digit == "04"
(1,248,472 observations deleted)

. gen unemployment = real(value)
(226 missing values generated)

. drop series_id footnote_codes _series_id_last2digit

. drop value

. 
. frlink 1:1 year period fips, frame(bls_laborforce)
  (all observations in frame default matched)

. frget laborforce, from (bls_laborforce)
(226 missing values generated)
  (1 variable copied from linked frame)

. 
. *merge 1:1 year period fips using bls_laborforce.dta
. 
. gen month = real(substr(period,2,2))

. capture drop yearmonth

. gen yearmonth = 100*year + month

. drop period

. sort fips yearmonth

. 
. drop if yearmonth<201801
(1,081,056 observations deleted)

. drop if yearmonth>202012
(51,520 observations deleted)

. 
. frame copy default unemployment_labor_county, replace
(note: frame unemployment_labor_county not found)

. 
. // *county level unemployment and prior unemployment
. // preserve
. keep if yearmonth>=201801 & yearmonth<=201906
(57,954 observations deleted)

. gen _unemploymentrate = unemployment/laborforce

. sort fips year month

. by fips year: egen unemploymentrate = mean(_unemploymentrate)

. 
. duplicates drop fips year, force

Duplicates in terms of fips year

(51,504 observations deleted)

. 
. gen _2019 = (year == 2019)

. gen _2018 = (year == 2018)

. 
. sort fips year

. capture drop unemploymentrate2018

. by fips: egen unemploymentrate2018 = sum(unemploymentrate*_2018)

. by fips: egen unemploymentrate2019h1 = sum(unemploymentrate*_2019)

. keep fips unemploymentrate2018 unemploymentrate2019h1

. duplicates drop fips, force

Duplicates in terms of fips

(3,219 observations deleted)

. frame copy default prior_unemployment, replace
(note: frame prior_unemployment not found)

. 
. frame change unemployment_labor_county

. frame copy unemployment_labor_county default, replace

. frame change default

. drop if yearmonth<201906
(54,723 observations deleted)

. frlink m:1 fips, frame(prior_unemployment)
  (24 observations in frame default unmatched)

. frget unemploymentrate2018 unemploymentrate2019h1, from(prior_unemployment)
(24 missing values generated)
(24 missing values generated)
  (2 variables copied from linked frame)

. 
. frame drop prior_unemployment

. 
. // capture drop year_lead1
. // gen year_lead1 = year if month !=12
. // replace year_lead1 = year+1 if month ==12
. //
. // capture drop month_lead1
. // gen month_lead1 = month+1 if month != 12
. // replace month_lead1 = 1 if month == 12
. //
. // gen yearmonth_lead1 = year_lead1 *100 +month_lead1
. gen unemploymentrate = unemployment/laborforce
(156 missing values generated)

. 
. sort fips yearmonth

. by fips: gen unemploymentrate_lag1 = unemploymentrate[_n-1]
(3,377 missing values generated)

. 
. keep fips yearmonth unemploymentrate_lag1 unemploymentrate unemploymentrate2018 unemploymentrate2019h1 

. // drop year month bls_laborforce prior_unemployment year_lead1 month_lead1
. 
. save unemployment_county0601.dta, replace
(file unemployment_county0601.dta not found)
file unemployment_county0601.dta saved

. 
. // zip3 level unemployment
. 
. 
. 
. // *zip 3 level unemployment
. frame change unemployment_labor_county

. frame copy unemployment_labor_county default, replace

. frame change default

. keep if yearmonth>=201801 & yearmonth<=202012
(0 observations deleted)

. 
. joinby fips using county_zip_link.dta

. 
. sort zip yearmonth 

. gen laborforce_zip5 = laborforce*res_ratio
(572 missing values generated)

. gen unemployment_zip5 = unemployment*res_ratio
(572 missing values generated)

. 
. gen zip3 = int(real(zip)/100)

. sort zip3 yearmonth zip

. 
. by zip3 yearmonth: egen laborforce_zip3 = total(laborforce_zip5)

. by zip3 yearmonth: egen unemployment_zip3 = total(unemployment_zip5)

. 
. drop laborforce_zip5 laborforce unemployment_zip5 unemployment zip

. 
. gen _unemploymentrate = unemployment_zip3/laborforce_zip3
(6,656 missing values generated)

. sort zip3 year month

. drop county fips res_ratio

. duplicates drop zip3 yearmonth, force

Duplicates in terms of zip3 yearmonth

(1,903,296 observations deleted)

. 
. *drop if laborforce_zip == 0 | laborforce_zip == .
. 
. *frame create unemployment_labor_zip, replace
. frame copy default unemployment_labor_zip3, replace
(note: frame unemployment_labor_zip3 not found)

. 
. keep if yearmonth>=201801 & yearmonth<=201906
(16,362 observations deleted)

. by zip3 year: egen unemploymentrate_zip3 = mean(_unemploymentrate)
(270 missing values generated)

. duplicates drop zip3 year, force

Duplicates in terms of zip3 year

(14,544 observations deleted)

. 
. gen _2019 = (year == 2019)

. gen _2018 = (year == 2018)

. 
. sort zip3 year

. capture drop unemploymentrate2018_zip3

. by zip3: egen unemploymentrate2018_zip3 = sum(unemploymentrate_zip3*_2018)

. by zip3: egen unemploymentrate2019h1_zip3 = sum(unemploymentrate_zip3*_2019)

. keep zip3 unemploymentrate2018_zip3 unemploymentrate2019h1_zip3

. duplicates drop zip3, force

Duplicates in terms of zip3

(909 observations deleted)

. frame copy default prior_unemployment, replace
(note: frame prior_unemployment not found)

. 
. frame change unemployment_labor_zip3

. frlink m:1 zip3, frame(prior_unemployment)
  (all observations in frame unemployment_labor_zip3 matched)

. frget unemploymentrate2018_zip3 unemploymentrate2019h1_zip3, from(prior_unemployment)
  (2 variables copied from linked frame)

. frame drop prior_unemployment

. 
. // capture drop year_lead1
. // gen year_lead1 = year if month !=12
. // replace year_lead1 = year+1 if month ==12
. //
. // capture drop month_lead1
. // gen month_lead1 = month+1 if month != 12
. // replace month_lead1 = 1 if month == 12
. //
. // gen yearmonth_lead1 = year_lead1 *100 +month_lead1
. gen unemploymentrate_zip3 = unemployment_zip3/laborforce_zip3
(546 missing values generated)

. sort zip3 yearmonth

. by zip3: gen unemploymentrate_zip3_lag1 = unemploymentrate_zip3[_n-1]
(1,440 missing values generated)

. keep zip3 yearmonth unemploymentrate_zip3_lag1 unemploymentrate_zip3 unemploymentrate2018_zip3 unemploymentrate2019h1_zip3 

. // drop year month bls_laborforce prior_unemployment year_lead1 month_lead1
. 
. drop if yearmonth < 201901
(10,908 observations deleted)

. // su unemploymentrate_zip3 unemploymentrate2018_zip3 unemploymentrate2019h1_zip3 
. save unemployment_zip30601.dta, replace
(file unemployment_zip30601.dta not found)
file unemployment_zip30601.dta saved

. 
end of do-file

. 
. **#T2 col1&2 
. frame reset

. 
. 
. use complaints_20190601_20201231_narrative_indicator2.dta, clear

. 
. gen _date = date(datereceived, "MD20Y") 

. format _date %d

. drop if _date < date("20190801", "YMD")
(48,433 observations deleted)

. drop if _date > date("20200930", "YMD")
(132,692 observations deleted)

. 
. drop datereceived subproduct - companypublicresponse

. drop tags - consumerdisputed

. compress
  variable _date was float now int
  (860,172 bytes saved)

. save "$datapath\complaints.dta", replace 
file ...\Data and code for publication_2025JAR\complaints.dta
    saved

. 
. 
. **#import data for T2 Column 3 & 4, Gallup survey
. frame reset

. import delimited "CFPB_20190101_20201031.csv", clear 
(encoding automatically selected: ISO-8859-1)
(17 vars, 631,060 obs)

. 
. gen _date = date(datereceived, "MD20Y") 

. format _date %d

. drop if _date >= date("20190601", "YMD")
(521,747 observations deleted)

. drop if _date < date("20190301", "YMD")
(39,138 observations deleted)

. sort _date

. drop _date

. frame copy default b4_20190601, replace
(note: frame b4_20190601 not found)

. 
. use complaints_20190601_20201231_narrative_indicator2.dta, clear

. frameappend b4_20190601

. gen _date = date(datereceived, "MD20Y") 

. format _date %d

. sort _date

. keep if (_date >= date("20200301", "YMD") & _date <= date("20200930", "YMD")) | (_date >= date("20190301", "YMD") & _date <= dat
> e("20190930", "YMD"))
(254,181 observations deleted)

. save "$datapath\complaints_gallup.dta", replace 
file ...\Data and code for
    publication_2025JAR\complaints_gallup.dta saved

. 
. 
. *complaint panel, from 2012 to 202003
. **#panel from 2012 to pre-covd (Feb 2020) or the end of 2020
. frame reset

. cd "$datapath"
...\Data and code for publication_2025JAR

. use "CFPB_complaints_20210616.dta", clear

. 
. generate _date = date(datereceived, "YMD")

. format %td _date

. sort _date

. 
. drop if _date < date("20120101", "YMD")
(2,536 observations deleted)

. drop if _date >= date("20200301", "YMD")
(578,132 observations deleted)

. drop datereceived subproduct - companypublicresponse

. drop tags - consumerdisputed

. drop company

. compress
  variable _date was float now int
  (3,045,316 bytes saved)

. gen month = month(_date)

. gen year = year(_date)

. gen yearmonth=100*year+month

. sort _date yearmonth zipcode

. 
. drop if state == "AA"
(27 observations deleted)

. drop if state == "AE"
(543 observations deleted)

. drop if state == "AS"
(32 observations deleted)

. drop if state == "AP"
(397 observations deleted)

. drop if state == "FM"
(115 observations deleted)

. drop if state == "GU"
(206 observations deleted)

. drop if state == "MH"
(31 observations deleted)

. drop if state == "MP"
(34 observations deleted)

. drop if state == "UNITED STATES MINOR OUTLYING ISLANDS"
(57 observations deleted)

. drop if state == "None"
(0 observations deleted)

. drop if state == "PR"
(3,723 observations deleted)

. drop if state == "PW"
(13 observations deleted)

. drop if state == "VI"
(262 observations deleted)

. drop if state == ""
(25,699 observations deleted)

. 
. sort zipcode

. drop if zipcode == "None"
(0 observations deleted)

. drop if zipcode == ""
(118,037 observations deleted)

. generate non_numeric = indexnot(zipcode, "0123456789X")

. sort non_numeric zipcode yearmonth

. drop if non_numeric != 0 
(78 observations deleted)

. drop non_numeric

. gen _zip3 = real(substr(zipcode, 1,3)) if substr(zipcode,-1,.) == "X"
(801,633 missing values generated)

. gen _zip3b = real(substr(zipcode, 1,3)) if substr(zipcode,-1,.) == "-"
(1,373,404 missing values generated)

. gen _zip3c = int(real(zipcode)/100)
(571,771 missing values generated)

. su  _zip3 _zip3b  _zip3c

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
       _zip3 |    571,771    488.9385    298.5345          8        999
      _zip3b |          0
      _zip3c |    801,633    510.1512    307.3634          0        999

. replace _zip3 = 0 if _zip3 ==.
(801,633 real changes made)

. replace _zip3b = 0 if _zip3b ==.
(1,373,404 real changes made)

. replace _zip3c = 0 if _zip3c ==.
(571,771 real changes made)

. gen zip3 = _zip3+_zip3b+ _zip3c

. drop _zip3 _zip3b  _zip3c

. sort zip3 yearmonth 

. 
. egen nb_totalcomplaints_mon = count(complaintid), by (zip3 yearmonth)

. 
. egen nb_bankaccount_mon = count(complaintid) if product == "Checking or savings account" | product == "Bank account or service",
>  ///
> by (zip3 yearmonth)
(1,241,605 missing values generated)

. sort zip3 yearmonth nb_bankaccount_mon

. bysort zip3 yearmonth: replace nb_bankaccount_mon = nb_bankaccount_mon[1]    
(1059613 real changes made)

. replace nb_bankaccount_mon = 0 if nb_bankaccount_mon == .
(181,992 real changes made)

. 
. sort zip3 yearmonth

. 
. keep zip3 yearmonth nb_totalcomplaints_mon nb_bankaccount_mon 

.                 
. duplicates drop yearmonth zip3, force

Duplicates in terms of yearmonth zip3

(1,299,462 observations deleted)

. 
. 
. set more off

. fillin zip3 yearmonth 

. 
. replace nb_totalcomplaints_mon = 0 if nb_totalcomplaints_mon == .
(21,706 real changes made)

. replace nb_bankaccount_mon = 0 if nb_bankaccount_mon == .
(21,706 real changes made)

. 
. gen year = int(yearmonth/100)

. 
. merge n:1 zip3 using zip3_to_state.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                         4,711
        from master                     4,704  (_merge==1)
        from using                          7  (_merge==2)

    Matched                            90,944  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(7 observations deleted)

. gen state = State
(4,704 missing values generated)

. drop _fillin State Distribution_Center Cities_Towns_Served source _merge

. gen zip3_num = zip3

. drop if state == ""
(4,704 observations deleted)

. drop if state == "AA"
(98 observations deleted)

. drop if state == "AE"
(784 observations deleted)

. drop if state == "AS"
(0 observations deleted)

. drop if state == "AP"
(490 observations deleted)

. drop if state == "FM"
(0 observations deleted)

. drop if state == "GU"
(98 observations deleted)

. drop if state == "MH"
(0 observations deleted)

. drop if state == "MP"
(0 observations deleted)

. drop if state == "UNITED STATES MINOR OUTLYING ISLANDS"
(0 observations deleted)

. drop if state == "None"
(0 observations deleted)

. drop if state == "PR"
(196 observations deleted)

. drop if state == "PW"
(0 observations deleted)

. drop if state == "VI"
(98 observations deleted)

. drop if state == ""
(0 observations deleted)

. 
. merge n:1 zip3_num year using censuspanel_zip3_residual.dta, force
(note: variable zip3 was str3 in the using data, but will be float now)

    Result                      Number of obs
    -----------------------------------------
    Not matched                         4,159
        from master                     2,352  (_merge==1)
        from using                      1,807  (_merge==2)

    Matched                            86,828  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(1,807 observations deleted)

. drop if _merge == 1
(2,352 observations deleted)

. drop _merge 

. sort zip3 yearmonth

. 
. gen complaints_percapita = nb_totalcomplaints_mon/population_zip3*100000

. gen nb_bankaccount_percapita = nb_bankaccount_mon/population_zip3*100000

. replace percapitaincome_zip3 = percapitaincome_zip3/1000000
(86,828 real changes made)

. 
. merge 1:1 zip3 yearmonth using covid_jhu_zip3_month_casenumber.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                        94,192
        from master                    85,056  (_merge==1)
        from using                      9,136  (_merge==2)

    Matched                             1,772  (_merge==3)
    -----------------------------------------

. *covid_zip3_month_casenumber.dta
. drop if _merge == 2
(9,136 observations deleted)

. drop _merge

. replace zip3_covid_mon = 0 if zip3_covid_mon == . & yearmonth <202001
(85,056 real changes made)

. replace zip3_covid_mon_lag1 = 0 if zip3_covid_mon_lag1 == . & yearmonth < 202001
(85,056 real changes made)

. gen positive_rate_zip3 =  zip3_covid_mon/population_zip3

. gen positive_rate_zip3_lag1 =  zip3_covid_mon_lag1/population_zip3

. 
. merge 1:1  zip3 yearmonth using unemployment_monthly.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                        22,252
        from master                         0  (_merge==1)
        from using                     22,252  (_merge==2)

    Matched                            86,828  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(22,252 observations deleted)

. drop _merge

. 
. merge n:1  zip3 year using unemployment_lag1yr.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                         1,116
        from master                         0  (_merge==1)
        from using                      1,116  (_merge==2)

    Matched                            86,828  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(1,116 observations deleted)

. drop _merge

. 
. gen _month = yearmonth - year * 100

. gen year_deposit = year - 1 if _month <= 6
(42,528 missing values generated)

. replace year_deposit = year if _month > 6
(42,528 real changes made)

. 
. merge n:1 zip3 year_deposit using deposit_zip3_2011to2022.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                         2,927
        from master                       196  (_merge==1)
        from using                      2,731  (_merge==2)

    Matched                            86,632  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(2,731 observations deleted)

. drop _merge

. gen deposit_percapita_zip3 = deposit_zip3/(population*1000)
(196 missing values generated)

. 
. winsor2 nb_totalcomplaints_mon complaints_percapita nb_bankaccount_percapita ///
>         nonwhite_pct_zip3 median_age_zip3 africanamerican_pct1_zip3 /// 
>         elderly_pct_zip3 percapitaincome_zip3 bachelordegree_pct_zip3 population_zip3 ///
>         unemploymentrate_zip3 unemploymentrate_zip3_l1yr unemploymentrate_zip3_l1m ///
>         positive_rate_zip3 positive_rate_zip3_lag1 deposit_percapita_zip3, suffix(_w) cuts(1 99)

. 
. egen state_month = group(state yearmonth)

. save complaints2012.dta, replace
file complaints2012.dta saved

. 
. **#firm-month-zip3 panel
. use "complaints_20190601_20201231_narrative_indicator2.dta", clear

. 
. gen _date = date(datereceived, "MD20Y") 

. format _date %d

. drop if _date < date("20190701", "YMD")
(23,350 observations deleted)

. drop if _date > date("20201031", "YMD")
(88,711 observations deleted)

. 
. gen month = month(_date)

. gen year = year(_date)

. 
. gen yearmonth=100*year+month

. 
. sort _date yearmonth zipcode

. 
. drop if state == "AA"
(10 observations deleted)

. drop if state == "AE"
(112 observations deleted)

. drop if state == "AS"
(5 observations deleted)

. drop if state == "AP"
(67 observations deleted)

. drop if state == "FM"
(0 observations deleted)

. drop if state == "GU"
(20 observations deleted)

. drop if state == "MH"
(0 observations deleted)

. drop if state == "MP"
(0 observations deleted)

. drop if state == "UNITED STATES MINOR OUTLYING ISLANDS"
(52 observations deleted)

. drop if state == "None"
(13,594 observations deleted)

. drop if state == "PR"
(941 observations deleted)

. drop if state == "PW"
(0 observations deleted)

. drop if state == "VI"
(76 observations deleted)

. drop if state == ""
(0 observations deleted)

. 
. drop if zipcode == "None"
(47,291 observations deleted)

. 
. gen _zip3 = real(substr(zipcode, 1,3)) if substr(zipcode,-1,.) == "X"
(226,080 missing values generated)

. gen _zip3b = real(substr(zipcode, 1,3)) if substr(zipcode,-1,.) == "-"
(436,981 missing values generated)

. gen _zip3c = int(real(zipcode)/100)
(210,903 missing values generated)

. su  _zip3 _zip3b  _zip3c

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
       _zip3 |    210,902    494.0901    290.3298         10        999
      _zip3b |          1         120           .        120        120
      _zip3c |    226,079     495.643    296.9031         10        999

. 
. replace _zip3 = 0 if _zip3 ==.
(226,080 real changes made)

. replace _zip3b = 0 if _zip3b ==.
(436,981 real changes made)

. replace _zip3c = 0 if _zip3c ==.
(210,903 real changes made)

. 
. gen zip3 = _zip3+_zip3b+ _zip3c

. 
. drop _zip3 _zip3b  _zip3c

. 
. sort zip3 yearmonth 

. 
. egen nb_totalcomplaints_mon = count(complaintid), by (zip3 yearmonth)

. 
. // match state to zip3
. merge n:1 zip3 using zip3_to_state.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                            37
        from master                         0  (_merge==1)
        from using                         37  (_merge==2)

    Matched                           436,982  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(37 observations deleted)

. 
. drop state

. 
. gen state = State

. drop  State Distribution_Center Cities_Towns_Served source _merge

. 
. drop if yearmonth>202009
(39,593 observations deleted)

. drop if yearmonth<201908
(21,643 observations deleted)

. sort zip3 yearmonth

. 
. 
. sort complaintid        

. gen zip3_num = zip3

. 
. /*clean zip3 matching*/
. merge n:1 zip3 using zip3_to_state.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                            38
        from master                         0  (_merge==1)
        from using                         38  (_merge==2)

    Matched                           375,746  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(38 observations deleted)

. rename state  state_cfpb

. gen state = State

. drop State Distribution_Center Cities_Towns_Served source _merge

. 
. drop state_cfpb

. 
. gen _val = 1

. egen _count_company = sum(_val), by (company)

. egen _count_total = sum(_val)

. 
. gen company_pct = _count_company/_count_total

. drop _val _count_total

. 
. gen company_alternative = company

. 
. gsort -company_pct

. 
. replace company_alternative = "others institutions" if company_pct<0.00025
(28,789 real changes made)

. 
. sort yearmonth zip3 company_alternative

. 
. by yearmonth zip3 company_alternative: egen nb_complaints_zip3_mon = count(complaintid)

. 
. sort yearmonth zip3 company_alternative

. 
. *fill the panel
. gen _year = floor(yearmonth/100)

. gen _month = mod(yearmonth, 100)

. 
. gen ym = ym(_year, _month)

. format ym %tm

. sort ym

. 
. /*create balanced panel*/
. egen x_dimension = group(zip3 company_alternative)

. duplicates drop ym zip3 company_alternative, force

Duplicates in terms of ym zip3 company_alternative

(252,021 observations deleted)

. 
. preserve

. keep zip3_num company_alternative x_dimension

. duplicates drop x_dimension, force

Duplicates in terms of x_dimension

(81,770 observations deleted)

. rename zip3_num zip3_num_fill 

. rename company_alternative company_alternative_fill

. tempfile zip3_company

. save `zip3_company'
file ...\AppData\Local\Temp\ST_38bc_000002.tmp saved as .dta format

. restore

. 
. tsset ym x_dimension 

Panel variable: ym (unbalanced)
 Time variable: x_dimension, 1 to 41955, but with gaps
         Delta: 1 unit

. tsfill, full

. 
. sort x_dimension ym

. merge n:1 x_dimension using `zip3_company', force

    Result                      Number of obs
    -----------------------------------------
    Not matched                             0
    Matched                           587,370  (_merge==3)
    -----------------------------------------

. drop _merge

. 
. drop datereceived - companypublicresponse zipcode - year

. 
. replace nb_complaints_zip3_mon = 0 if nb_complaints_zip3_mon ==.
(463,645 real changes made)

. 
. replace zip3 =  zip3_num_fill  if zip3 ==.
(463,645 real changes made)

. replace zip3_num =      zip3_num_fill  if zip3_num ==.
(463,645 real changes made)

. replace company_alternative = company_alternative_fill if company == ""
(463,645 real changes made)

. 
. gsort zip3 -state

. bysort zip3: replace state=state[_n-1] if missing(state) & _n > 1               
(463,645 real changes made)

.         
. merge n:1 zip3_num using census_zip3_covid.dta, force
(note: variable zip3 was str3 in the using data, but will be float now)

    Result                      Number of obs
    -----------------------------------------
    Not matched                         1,595
        from master                     1,582  (_merge==1)
        from using                         13  (_merge==2)

    Matched                           585,788  (_merge==3)
    -----------------------------------------

. 
. keep if _merge == 3 
(1,595 observations deleted)

. drop _merge

. 
. sort zip3 yearmonth

. 
. drop if population_zip3 == .
(0 observations deleted)

. drop if population_zip3 == 0
(0 observations deleted)

. 
. drop if elderly_pct_zip3 == .
(0 observations deleted)

. drop if percapitaincome_zip3 == .
(0 observations deleted)

. drop if nonwhite_pct_zip3 ==.
(0 observations deleted)

. drop if bachlordegree_pct_zip3 ==.
(0 observations deleted)

. 
. sort zip3 ym

. 
. egen state_month = group(state ym)

. 
. /*per capita complaints*/
. gen complaints_percapita_zip3 = nb_complaints_zip3_mon/population_zip3*100000

. 
. /*scale by 1 million*/
. replace percapitaincome_zip3 = percapitaincome_zip3 /1000000
(585,788 real changes made)

. 
. 
. capture drop yearmonth

. capture drop _year

. capture drop _month

. capture drop _date

. 
. gen _date = dofm(ym)

. format _date %d

. gen yearmonth = year(_date)*100 +month(_date)

. 
. 
. /*match covid data*/
. merge n:1 zip3 yearmonth using covid_jhu_zip3_month_casenumber.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                       212,234
        from master                   209,210  (_merge==1)
        from using                      3,024  (_merge==2)

    Matched                           376,578  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(3,024 observations deleted)

. drop _merge

. replace zip3_covid_mon = 0 if zip3_covid_mon == . & ym <202001
(209,210 real changes made)

. replace zip3_covid_mon_lag1 = 0 if zip3_covid_mon_lag1 == . & yearmonth < 202001
(209,210 real changes made)

. gen positive_rate_zip3 =  zip3_covid_mon/population_zip3

. gen positive_rate_zip3_lag1 =  zip3_covid_mon_lag1/population_zip3

. 
. merge n:1  zip3 yearmonth using unemployment_zip30601.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                         9,552
        from master                         0  (_merge==1)
        from using                      9,552  (_merge==2)

    Matched                           585,788  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(9,552 observations deleted)

. drop _merge

. 
. merge n:1 zip3_num using deposit_zip3.dta

    Result                      Number of obs
    -----------------------------------------
    Not matched                            15
        from master                         0  (_merge==1)
        from using                         15  (_merge==2)

    Matched                           585,788  (_merge==3)
    -----------------------------------------

. drop if _merge == 2
(15 observations deleted)

. drop _merge

. 
. gen deposit2019_percapita_zip3 = deposit_2019_zip3/(population*1000)

. gen deposit2020_percapita_zip3 = deposit_2020_zip3/(population*1000)

. 
. gen deposit_percapita_zip3 = deposit2019_percapita_zip3 if yearmonth <= 202006
(125,526 missing values generated)

. replace deposit_percapita_zip3 = deposit2020_percapita_zip3 if yearmonth>= 202007
(125,526 real changes made)

. 
. drop if deposit_percapita_zip3 == .
(0 observations deleted)

. 
. winsor2 complaints_percapita ///
>         nonwhite_pct_zip3 africanamerican_pct1_zip3 ///
>         elderly_pct_zip3 percapitaincome_zip3 bachlordegree_pct_zip3 population_zip3 ///
>         unemploymentrate2018_zip3 unemploymentrate2019h1_zip3 unemploymentrate_zip3 unemploymentrate_zip3_lag1 ///
>         deposit_percapita_zip3 positive_rate_zip3 positive_rate_zip3_lag1 , suffix(_w) cuts(1 99)

.         
. gen d_post_ne = 1 if ym >721
(292,894 missing values generated)

. replace d_post_ne = 0 if d_post_ne ==.  
(292,894 real changes made)

. 
. label var d_post_ne "COVID period"

. label var nonwhite_pct_zip3_w "Nonwhite"

. // label var unemployment_rate_zip3_w "Unemployment rate"
. // label var positive_rate_zip3_w "Covid positive rate"
. label var positive_rate_zip3_w "COVID-19 positive rate (t)"

. label var positive_rate_zip3_lag1_w "COVID-19 positive rate (t-1)"

. label var unemploymentrate_zip3_w "Unemployment rate (t)"

. label var unemploymentrate_zip3_lag1_w "Unemployment rate (t-1)"

. label var unemploymentrate2018_zip3_w "Pre-COVID unemployment rate (2018)"

. label var unemploymentrate2019h1_zip3_w "Pre-COVID unemployment rate (2019H1)"

. 
. *label var d_bw_ne_gf_nonwhite "Dummy: Between National Emergency Declaration (NE) and Geoge Floyd Event (GF) * Non-white percen
> tage"                                   
. gen d_post_ne_nonwhite = d_post_ne*c.nonwhite_pct_zip3_w                                                        

. label var d_post_ne_nonwhite "COVID period * Nonwhite"  

. 
. gen d_post_ne_africanamerican_pct1 = d_post_ne*africanamerican_pct1_zip3_w

. label var d_post_ne_africanamerican_pct1 "COVID period * African American"

. 
. gen d_post_ne_elderly = d_post_ne*elderly_pct_zip3_w                                                    

. label var d_post_ne_elderly "COVID period * Elderly"                                                    

. 
. gen d_post_ne_percapitaincome = d_post_ne*percapitaincome_zip3_w                                                        

. label var d_post_ne_percapitaincome "COVID period * Per capita income"                                                  

.                                                                                                 
. gen d_post_ne_bachlor = d_post_ne*bachlordegree_pct_zip3_w                                                      

. label var d_post_ne_bachlor "COVID period * Bachelor"                                                   

.                 
. gen d_post_ne_unemployment = d_post_ne*c.unemploymentrate_zip3_w                        

. label var d_post_ne_unemployment "COVID period * Unemployment rate (t)"

.                         
. gen d_post_ne_unemployment_lag1 = d_post_ne*c.unemploymentrate_zip3_lag1_w                      

. label var d_post_ne_unemployment_lag1 "COVID period * Unemployment rate (t-1)"                  

.                         
. gen d_post_ne_priunemployment2018 = d_post_ne*c.unemploymentrate2018_zip3_w                     

. label var d_post_ne_priunemployment2018 "COVID period * Pre-COVID unemployment rate (2018)"

. 
. gen d_post_ne_priunemployment2019h1 = d_post_ne*c.unemploymentrate2019h1_zip3_w         

. label var d_post_ne_priunemployment2019h1 "COVID period * Pre-COVID unemployment rate (2019H1)"

. 
. gen d_post_ne_deposit_percapita = d_post_ne*deposit_percapita_zip3_w

. label var d_post_ne_deposit_percapita "COVID period * Per capita deposit"

. 
. 
. rename company_alternative_fill company_cfpb 

. 
. sort company_cfpb zip3_num ym           

.         
. drop company _count_company company_pct x_dimension zip3_num_fill

. 
. save company_zip_month.dta, replace
file company_zip_month.dta saved

. 
. 
. 
.**T6
. use "treat_control_list.dta", clear

. gen low = yearmonth - 111

. gen high = yearmonth + 100

. 
. rangejoin yearmonth low high using "company_zip_month_longpanel20122021.dta", by(company_alternative)
  (using rangestat version 1.1.1)

. save "firm_complaint_long_panel_treat_control.dta", replace
file firm_complaint_long_panel_treat_control.dta saved

 
end of do-file

. . import delimited "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_cov
> id_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv", clear
(encoding automatically selected: ISO-8859-1)
(1,154 vars, 3,342 obs)

. sort fips

. duplicates list fips

Duplicates in terms of fips

  +-------------+
  |  Obs   fips |
  |-------------|
  | 3333      . |
  | 3334      . |
  | 3335      . |
  | 3336      . |
  | 3337      . |
  |-------------|
  | 3338      . |
  | 3339      . |
  | 3340      . |
  | 3341      . |
  | 3342      . |
  +-------------+

. drop if fips == .
(10 observations deleted)

. 
. foreach i of varlist _all {
  2. local a : variable label `i'
  3. local a: subinstr local a "/" "_", all
  4. label var `i' "`a'"
  5. }

. 
. foreach v of varlist v12-v765 {
  2.    local x : variable label `v'
  3.    rename `v' d`x'
  4. }

. 
. *save jhu_covidconfirmedcases.dta, replace
. 
. reshape long d, i(fips) j(_date) string
(j = 10_10_20 10_10_21 10_11_20 10_11_21 10_12_20 10_12_21 10_13_20 10_13_21 10_14_20 10_14_2
> 1 10_15_20 10_15_21 10_16_20 10_16_21 10_17_20 10_17_21 10_18_20 10_18_21 10_19_20 10_19_21
>  10_1_20 10_1_21 10_20_20 10_20_21 10_21_20 10_21_21 10_22_20 10_22_21 10_23_20 10_23_21 10
> _24_20 10_24_21 10_25_20 10_25_21 10_26_20 10_26_21 10_27_20 10_27_21 10_28_20 10_28_21 10_
> 29_20 10_29_21 10_2_20 10_2_21 10_30_20 10_30_21 10_31_20 10_31_21 10_3_20 10_3_21 10_4_20 
> 10_4_21 10_5_20 10_5_21 10_6_20 10_6_21 10_7_20 10_7_21 10_8_20 10_8_21 10_9_20 10_9_21 11_
> 10_20 11_10_21 11_11_20 11_11_21 11_12_20 11_12_21 11_13_20 11_13_21 11_14_20 11_14_21 11_1
> 5_20 11_15_21 11_16_20 11_16_21 11_17_20 11_17_21 11_18_20 11_18_21 11_19_20 11_19_21 11_1_
> 20 11_1_21 11_20_20 11_20_21 11_21_20 11_21_21 11_22_20 11_22_21 11_23_20 11_23_21 11_24_20
>  11_24_21 11_25_20 11_25_21 11_26_20 11_26_21 11_27_20 11_27_21 11_28_20 11_28_21 11_29_20 
> 11_29_21 11_2_20 11_2_21 11_30_20 11_30_21 11_3_20 11_3_21 11_4_20 11_4_21 11_5_20 11_5_21 
> 11_6_20 11_6_21 11_7_20 11_7_21 11_8_20 11_8_21 11_9_20 11_9_21 12_10_20 12_10_21 12_11_20 
> 12_11_21 12_12_20 12_12_21 12_13_20 12_13_21 12_14_20 12_14_21 12_15_20 12_15_21 12_16_20 1
> 2_16_21 12_17_20 12_17_21 12_18_20 12_18_21 12_19_20 12_19_21 12_1_20 12_1_21 12_20_20 12_2
> 0_21 12_21_20 12_21_21 12_22_20 12_22_21 12_23_20 12_23_21 12_24_20 12_24_21 12_25_20 12_25
> _21 12_26_20 12_26_21 12_27_20 12_27_21 12_28_20 12_28_21 12_29_20 12_29_21 12_2_20 12_2_21
>  12_30_20 12_30_21 12_31_20 12_31_21 12_3_20 12_3_21 12_4_20 12_4_21 12_5_20 12_5_21 12_6_2
> 0 12_6_21 12_7_20 12_7_21 12_8_20 12_8_21 12_9_20 12_9_21 1_10_21 1_10_22 1_11_21 1_11_22 1
> _12_21 1_12_22 1_13_21 1_13_22 1_14_21 1_14_22 1_15_21 1_15_22 1_16_21 1_16_22 1_17_21 1_17
> _22 1_18_21 1_18_22 1_19_21 1_19_22 1_1_21 1_1_22 1_20_21 1_20_22 1_21_21 1_21_22 1_22_20 1
> _22_21 1_22_22 1_23_20 1_23_21 1_23_22 1_24_20 1_24_21 1_24_22 1_25_20 1_25_21 1_25_22 1_26
> _20 1_26_21 1_26_22 1_27_20 1_27_21 1_27_22 1_28_20 1_28_21 1_28_22 1_29_20 1_29_21 1_29_22
>  1_2_21 1_2_22 1_30_20 1_30_21 1_30_22 1_31_20 1_31_21 1_31_22 1_3_21 1_3_22 1_4_21 1_4_22 
> 1_5_21 1_5_22 1_6_21 1_6_22 1_7_21 1_7_22 1_8_21 1_8_22 1_9_21 1_9_22 2_10_20 2_10_21 2_10_
> 22 2_11_20 2_11_21 2_11_22 2_12_20 2_12_21 2_12_22 2_13_20 2_13_21 2_13_22 2_14_20 2_14_21 
> 2_15_20 2_15_21 2_16_20 2_16_21 2_17_20 2_17_21 2_18_20 2_18_21 2_19_20 2_19_21 2_1_20 2_1_
> 21 2_1_22 2_20_20 2_20_21 2_21_20 2_21_21 2_22_20 2_22_21 2_23_20 2_23_21 2_24_20 2_24_21 2
> _25_20 2_25_21 2_26_20 2_26_21 2_27_20 2_27_21 2_28_20 2_28_21 2_29_20 2_2_20 2_2_21 2_2_22
>  2_3_20 2_3_21 2_3_22 2_4_20 2_4_21 2_4_22 2_5_20 2_5_21 2_5_22 2_6_20 2_6_21 2_6_22 2_7_20
>  2_7_21 2_7_22 2_8_20 2_8_21 2_8_22 2_9_20 2_9_21 2_9_22 3_10_20 3_10_21 3_11_20 3_11_21 3_
> 12_20 3_12_21 3_13_20 3_13_21 3_14_20 3_14_21 3_15_20 3_15_21 3_16_20 3_16_21 3_17_20 3_17_
> 21 3_18_20 3_18_21 3_19_20 3_19_21 3_1_20 3_1_21 3_20_20 3_20_21 3_21_20 3_21_21 3_22_20 3_
> 22_21 3_23_20 3_23_21 3_24_20 3_24_21 3_25_20 3_25_21 3_26_20 3_26_21 3_27_20 3_27_21 3_28_
> 20 3_28_21 3_29_20 3_29_21 3_2_20 3_2_21 3_30_20 3_30_21 3_31_20 3_31_21 3_3_20 3_3_21 3_4_
> 20 3_4_21 3_5_20 3_5_21 3_6_20 3_6_21 3_7_20 3_7_21 3_8_20 3_8_21 3_9_20 3_9_21 4_10_20 4_1
> 0_21 4_11_20 4_11_21 4_12_20 4_12_21 4_13_20 4_13_21 4_14_20 4_14_21 4_15_20 4_15_21 4_16_2
> 0 4_16_21 4_17_20 4_17_21 4_18_20 4_18_21 4_19_20 4_19_21 4_1_20 4_1_21 4_20_20 4_20_21 4_2
> 1_20 4_21_21 4_22_20 4_22_21 4_23_20 4_23_21 4_24_20 4_24_21 4_25_20 4_25_21 4_26_20 4_26_2
> 1 4_27_20 4_27_21 4_28_20 4_28_21 4_29_20 4_29_21 4_2_20 4_2_21 4_30_20 4_30_21 4_3_20 4_3_
> 21 4_4_20 4_4_21 4_5_20 4_5_21 4_6_20 4_6_21 4_7_20 4_7_21 4_8_20 4_8_21 4_9_20 4_9_21 5_10
> _20 5_10_21 5_11_20 5_11_21 5_12_20 5_12_21 5_13_20 5_13_21 5_14_20 5_14_21 5_15_20 5_15_21
>  5_16_20 5_16_21 5_17_20 5_17_21 5_18_20 5_18_21 5_19_20 5_19_21 5_1_20 5_1_21 5_20_20 5_20
> _21 5_21_20 5_21_21 5_22_20 5_22_21 5_23_20 5_23_21 5_24_20 5_24_21 5_25_20 5_25_21 5_26_20
>  5_26_21 5_27_20 5_27_21 5_28_20 5_28_21 5_29_20 5_29_21 5_2_20 5_2_21 5_30_20 5_30_21 5_31
> _20 5_31_21 5_3_20 5_3_21 5_4_20 5_4_21 5_5_20 5_5_21 5_6_20 5_6_21 5_7_20 5_7_21 5_8_20 5_
> 8_21 5_9_20 5_9_21 6_10_20 6_10_21 6_11_20 6_11_21 6_12_20 6_12_21 6_13_20 6_13_21 6_14_20 
> 6_14_21 6_15_20 6_15_21 6_16_20 6_16_21 6_17_20 6_17_21 6_18_20 6_18_21 6_19_20 6_19_21 6_1
> _20 6_1_21 6_20_20 6_20_21 6_21_20 6_21_21 6_22_20 6_22_21 6_23_20 6_23_21 6_24_20 6_24_21 
> 6_25_20 6_25_21 6_26_20 6_26_21 6_27_20 6_27_21 6_28_20 6_28_21 6_29_20 6_29_21 6_2_20 6_2_
> 21 6_30_20 6_30_21 6_3_20 6_3_21 6_4_20 6_4_21 6_5_20 6_5_21 6_6_20 6_6_21 6_7_20 6_7_21 6_
> 8_20 6_8_21 6_9_20 6_9_21 7_10_20 7_10_21 7_11_20 7_11_21 7_12_20 7_12_21 7_13_20 7_13_21 7
> _14_20 7_14_21 7_15_20 7_15_21 7_16_20 7_16_21 7_17_20 7_17_21 7_18_20 7_18_21 7_19_20 7_19
> _21 7_1_20 7_1_21 7_20_20 7_20_21 7_21_20 7_21_21 7_22_20 7_22_21 7_23_20 7_23_21 7_24_20 7
> _24_21 7_25_20 7_25_21 7_26_20 7_26_21 7_27_20 7_27_21 7_28_20 7_28_21 7_29_20 7_29_21 7_2_
> 20 7_2_21 7_30_20 7_30_21 7_31_20 7_31_21 7_3_20 7_3_21 7_4_20 7_4_21 7_5_20 7_5_21 7_6_20 
> 7_6_21 7_7_20 7_7_21 7_8_20 7_8_21 7_9_20 7_9_21 8_10_20 8_10_21 8_11_20 8_11_21 8_12_20 8_
> 12_21 8_13_20 8_13_21 8_14_20 8_14_21 8_15_20 8_15_21 8_16_20 8_16_21 8_17_20 8_17_21 8_18_
> 20 8_18_21 8_19_20 8_19_21 8_1_20 8_1_21 8_20_20 8_20_21 8_21_20 8_21_21 8_22_20 8_22_21 8_
> 23_20 8_23_21 8_24_20 8_24_21 8_25_20 8_25_21 8_26_20 8_26_21 8_27_20 8_27_21 8_28_20 8_28_
> 21 8_29_20 8_29_21 8_2_20 8_2_21 8_30_20 8_30_21 8_31_20 8_31_21 8_3_20 8_3_21 8_4_20 8_4_2
> 1 8_5_20 8_5_21 8_6_20 8_6_21 8_7_20 8_7_21 8_8_20 8_8_21 8_9_20 8_9_21 9_10_20 9_10_21 9_1
> 1_20 9_11_21 9_12_20 9_12_21 9_13_20 9_13_21 9_14_20 9_14_21 9_15_20 9_15_21 9_16_20 9_16_2
> 1 9_17_20 9_17_21 9_18_20 9_18_21 9_19_20 9_19_21 9_1_20 9_1_21 9_20_20 9_20_21 9_21_20 9_2
> 1_21 9_22_20 9_22_21 9_23_20 9_23_21 9_24_20 9_24_21 9_25_20 9_25_21 9_26_20 9_26_21 9_27_2
> 0 9_27_21 9_28_20 9_28_21 9_29_20 9_29_21 9_2_20 9_2_21 9_30_20 9_30_21 9_3_20 9_3_21 9_4_2
> 0 9_4_21 9_5_20 9_5_21 9_6_20 9_6_21 9_7_20 9_7_21 9_8_20 9_8_21 9_9_20 9_9_21)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations            3,332   ->   2,512,328   
Number of variables               1,154   ->   402         
j variable (754 values)                   ->   _date
xij variables:
        d10_10_20 d10_10_21 ... d9_9_21   ->   d
-----------------------------------------------------------------------------

. 
. gen _date2 = subinstr(_date, "_", "/", .)

. 
. gen date = date(_date2,"MDY", 2022)

. format %td date

. 
. sort fips date

. 
. rename d number

. 
. drop _date uid iso2 iso3 code3 admin2 province_state country_region lat long_ _date2

. // drop date beyond sep 10, 2021
. capture drop v609

. capture drop v610 - v765

. // save jhu_covid_fips.dta, replace
. //
. //
. //
. // use jhu_covid_fips.dta, clear
. drop if date > date("20201231","YMD")
(1,362,788 observations deleted)

. // capture drop week
. // gen week = floor((date-date("20200120","YMD"))/7)
. // gen dayofweek = mod(date-date("20200120","YMD"), 7)
. //
. // // keep friday data
. // keep if dayofweek == 4
. //
. // frlink m:1 fips, frame(county_census)
. // frget population, from (county_census)
. // capture drop positive_rate
. // gen positive_rate = number/(population/10000)
. // drop if positive_rate == .
. // su positive_rate if number>0 & yearmonth <202101
. 
. // *generate month end date
. gen _monthenddate=mdy( month(date)+1,1,year(date))-1 
(103,292 missing values generated)

. replace _monthenddate=mdy(12,31,year(date)) if month(date)==12
(103,292 real changes made)

. format %td _monthenddate

. 
. keep if _monthenddate == date
(1,109,556 observations deleted)

. 
. preserve

. keep if date == date("20200131","YMD")
(36,652 observations deleted)

. su number if number>0

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
      number |          7    1.142857    .3779645          1          2

. restore

. preserve

. keep if date == date("20200229","YMD")
(36,652 observations deleted)

. su number if number>0

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
      number |         16       1.625    1.310216          1          6

. restore

. preserve

. keep if date == date("20200331","YMD")
(36,652 observations deleted)

. su number if number>0

    Variable |        Obs        Mean    Std. dev.       Min        Max
-------------+---------------------------------------------------------
      number |      2,159     88.8856    605.9224          1      13691

. restore

. 
. // *generate previous month end, construct lag month
. // capture drop _lagmonthenddate
. // gen _lagmonthenddate = mdy(month(_monthenddate-45)+1,1,year(_monthenddate-45))-1
. // replace _lagmonthenddate=mdy(12,31,year(_monthenddate-45)) if month(_monthenddate-45)==12
. // format %td _lagmonthenddate
. 
. // capture drop _leadmonthenddate
. // gen _leadmonthenddate = mdy(month(_monthenddate+1)+1,1,year(_monthenddate+1))-1
. // replace _leadmonthenddate=mdy(12,31,year(_monthenddate+1)) if month(_monthenddate+1)==12
. // format %td _leadmonthenddate
. 
. // *generate yearmonth and lag-yearmonth
. 
. gen month = month(date)

. gen year = year(date)

. *egen _yearmonth = group (year month)
. gen yearmonth=100*year+month

. 
. // gen month_lag1 = month(_lagmonthenddate)
. // gen year_lag1 = year(_lagmonthenddate)
. // *egen _yearmonth = group (year month)
. // gen yearmonth_lag1=100*year_lag1+month_lag1
. 
. // gen month_lead1 = month(_leadmonthenddate)
. // gen year_lead1 = year(_leadmonthenddate)
. // gen yearmonth_lead1=100*year_lead1+month_lead1
. 
. rename combined_key countystate

. keep fips number date yearmonth 

. *yearmonth_lead1
.  
. preserve

. drop date

. rename number cnty_covid_mon 

. sort fips yearmonth

. by fips: gen cnty_covid_mon_lag1 = cnty_covid_mon[_n-1] 
(3,332 missing values generated)

. replace cnty_covid_mon_lag1 = 0 if yearmonth == 202001
(3,332 real changes made)

. save covid_jhu_county_month.dta, replace
(file covid_jhu_county_month.dta not found)
file covid_jhu_county_month.dta saved

. restore

. 
. 
.  
. // frame create covid_jhu_county_month
. frame copy default covid_jhu_county_month, replace
(note: frame covid_jhu_county_month not found)

.  
. // frame create usfact_county_covid
. // frame usfact_county_covid: use covid_county_month_casenumber.dta
. 
. // frame create county_zip_link
. // frame county_zip_link: use county_zip_link.dta
. 
. 
. joinby fips using county_zip_link.dta

. 
. sort fips yearmonth zip

. gen case_number_zip5 = number*res_ratio

. 
. preserve

. sort zip yearmonth

. by zip yearmonth: egen zip_covid_mon = total(case_number_zip5)

. duplicates drop zip yearmonth, force

Duplicates in terms of zip yearmonth

(172,236 observations deleted)

. keep zip yearmonth zip_covid_mon

. *yearmonth_lead1 
. sort zip yearmonth

. by zip: gen zip_covid_mon_lag1 = zip_covid_mon[_n-1]
(39,429 missing values generated)

. replace zip_covid_mon_lag1 = 0 if yearmonth == 202001
(39,429 real changes made)

. generate zipcode_num = real(zip)

. save covid_jhu_zip5_month_casenumber.dta, replace
(file covid_jhu_zip5_month_casenumber.dta not found)
file covid_jhu_zip5_month_casenumber.dta saved

. restore

. 
. // zip3 unemployment
. frame change covid_jhu_county_month

. frame copy covid_jhu_county_month default, replace

. frame change default

. joinby fips using county_zip_link.dta

. 
. sort fips yearmonth zip

. gen case_number_zip5 = number*res_ratio

. 
. preserve

. gen zip3 = int(real(zip)/100)

. sort zip3 yearmonth zip

. by zip3 yearmonth: egen zip3_covid_mon = total(case_number_zip5)

. duplicates drop zip3 yearmonth, force

Duplicates in terms of zip3 yearmonth

(634,476 observations deleted)

. keep zip3 yearmonth zip3_covid_mon 

. *yearmonth_lead1
. sort zip3 yearmonth

. by zip3: gen zip3_covid_mon_lag1 = zip3_covid_mon[_n-1]
(909 missing values generated)

. replace zip3_covid_mon_lag1 = 0 if yearmonth == 202001
(909 real changes made)

. save covid_jhu_zip3_month_casenumber.dta, replace
(file covid_jhu_zip3_month_casenumber.dta not found)
file covid_jhu_zip3_month_casenumber.dta saved

. restore

. 
. 
end of do-file

. 
