*Clear the dataset and load data clear graph drop _all set more off ****CPI**** import excel /// "C:\Users\320491\Desktop\Final NBER\(April 20 2019 - CPI by MSA - all items) SeriesReport-20190420174638_ea1e01.xlsx", /// sheet("transposed") firstrow rename CUUS*SA0 ph* save temp_cpi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\(April 20 2019 - CPI by MSA - all items less food and energy) SeriesReport-20190420175056_eb25fd.xlsx", /// sheet("transposed") firstrow clear rename CUUS*SA0L1E pc* merge 1:1 SeriesID using temp_cpi.dta save temp_cpi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\(April 20 2019 - CPI by MSA - services) SeriesReport-20190420175417_c48fa0.xlsx", /// sheet("transposed") firstrow clear rename CUUS*SAS ps* merge 1:1 SeriesID using temp_cpi.dta, generate(_merge2) save temp_cpi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\(April 27 2019 - CPI by MSA - services less shelter) SeriesReport-20190426201715_9dbcda.xlsx", /// sheet("transposed") firstrow clear rename CUUS*SASL2RS psxr* merge 1:1 SeriesID using temp_cpi.dta, generate(_merge3) drop CUUR* rename SeriesID period drop if period=="" save temp_cpi.dta, replace drop if strpos(period, "Annual") drop if strpos(period, "HALF") gen month_year = date(period, "MY"), format month_year %td gen monthlyDate=mofd(month_year) format monthlyDate %tm tsset monthlyDate g halfyear = halfyear(month_year) g year = year(month_year) drop period foreach var of varlist _all { replace `var'=(l.`var'+f.`var')/2 /// if `var'==. & l.`var'!=. & f.`var'!=. replace `var'=-999999999999 if `var'==. } collapse (mean) p*, by(year halfyear) foreach var of varlist _all { replace `var'=. if `var'<0 } rename p* intpl_p* save temp_interpolation.dta, replace use temp_cpi.dta, clear drop if ~strpos(period, "HALF") g halfyear=1 if strpos(period, "HALF1") replace halfyear=2 if strpos(period, "HALF2") g year=substr(period,-4,4) destring year, replace drop period merge 1:1 year halfyear using temp_interpolation.dta, generate(_merge4) drop if year==2019 | year==1982 | year==1983 assert _merge==3 & _merge2==3 & _merge3==3 & _merge4==3 drop _merge* foreach var of varlist p* { replace `var'=intpl_`var' if `var'==. } drop intpl* g halfDate = yh(year, halfyear) reshape long ph pc ps psxr, i(halfDate) j(Area) s save temp_cpi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\CPI MSA to Unemployment (OMB) MSA.xlsx", /// sheet("Codes") firstrow clear drop OMBMSA2 OMBMSA3 merge 1:m Area using temp_cpi.dta, assert(master match) drop _merge sort Area halfDate reshape long OMBMSA, i(Area halfDate) j(number) rename OMBMSA msa_code save temp_cpi.dta, replace ****UNEMPLOYMENT**** clear import excel /// "C:\Users\320491\Desktop\Final NBER\(April 21 2019 - Unemployment by MSA) SeriesReport-20190420211155_55ec72.xlsx", /// sheet("transposed") firstrow rename SeriesID period drop if period=="" gen month_year = date(period, "MY"), format month_year %td gen monthlyDate=mofd(month_year) format monthlyDate %tm tsset monthlyDate g halfyear = halfyear(month_year) g year = year(month_year) drop if year==2019 drop period *00000005 rename LAUMT*00000003 ur* rename LAUMT*00000004 un* rename LAUMT*00000006 lf* rename LAUMC*00000003 ur* rename LAUMC*00000004 un* rename LAUMC*00000006 lf* foreach var of varlist ur* { local varname=substr("`var'",5,5) rename `var' ur`varname' } foreach var of varlist un* { local varname=substr("`var'",5,5) rename `var' un`varname' } foreach var of varlist lf* { local varname=substr("`var'",5,5) rename `var' lf`varname' } reshape long ur un lf, i(monthlyDate) j(msa_code) assert round(ur-100*un/lf,0.1)==0 replace ur=100*un/lf collapse (mean) ur un lf, by(msa_code year halfyear) assert !missing(ur) assert !missing(lf) g halfDate = yh(year, halfyear) merge 1:m msa_code halfDate using temp_cpi.dta sort Area number halfDate egen double total_lf = total(lf), by(halfDate Area) g w_lf = lf/total_lf collapse (rawsum) un lf (sum) ur ph pc ps psxr (median) year halfyear [iweight=w_lf], /// by(halfDate Area) replace ph=. if ph==0 replace pc=. if pc==0 replace ps=. if ps==0 replace psxr=. if psxr==0 sort Area halfDate egen area_id = group(Area) save temp_cpi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\April 26 2019 - UMich one year inflation expectations.xlsx", /// sheet(Data) firstrow cellrange(A4:F508) clear drop if Datemy==. drop Recessions save temp_epi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\April 26 2019 - UMich five year inflation expectations.xlsx", /// sheet(Data) firstrow cellrange(A4:F508) clear drop if Datemy==. drop Recessions merge 1:1 Datemy using temp_epi.dta, assert(match) drop _merge rename *_MD_REG* ** g halfyear = halfyear(Datemy) g year = year(Datemy) collapse (mean) PX*, by(year halfyear) g halfDate = yh(year, halfyear) reshape long PX PX5, i(halfDate) j(region) s save temp_epi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\CPI MSA to Census region.xlsx", /// sheet("Codes") firstrow clear merge 1:m Area using temp_cpi.dta, assert(using match) drop _merge merge m:1 region halfDate using temp_epi.dta, keep(match) drop _merge save temp_cpi.dta, replace import excel /// "C:\Users\320491\Desktop\Final NBER\CPI MSA to Unemployment (OMB) MSA.xlsx", /// sheet("CPI codes") firstrow clear rename CPIArea area_name rename Code Area merge 1:m Area using temp_cpi.dta, keep(match) drop _merge order halfDate year halfyear area_id area_name ph pc ps psxr PX PX5 ur un lf xtset area_id halfDate g pih=200*(ln(ph)-ln(l.ph)) g pic=200*(ln(pc)-ln(l.pc)) g pis=200*(ln(ps)-ln(l.ps)) g pisxr=200*(ln(psxr)-ln(l.psxr)) rename PX* epi* foreach var of varlist pi* ur { g l`var'=l.`var' g l2`var'=l2.`var' g l3`var'=l3.`var' label var l`var' "First lag" label var l2`var' "Second lag" label var l3`var' "Third lag" } label var pih "CPI inflation" label var pic "Core CPI inflation" label var pis "Services CPI inflation" label var pisxr "Services CPI excluding shelter inflation" label var un "Unemployed persons" label var ur "Unemployment rate" label var lf "Labor force" label var ur "Unemployment rate" label var ph "CPI level" label var pc "Core CPI level" label var ps "Services CPI level" label var psxr "Services CPI excluding shelter level" label var epi "1 year ahead inflation expectations" label var epi5 "5 year ahead inflation expectations" label var area_id "MSA ID" label var area_name "MSA name" label var halfDate "Semi-annual period" label var year "Year" label var halfyear "Half year" save "C:\Users\320491\Desktop\Final NBER\checking\checkingNBERMetroData.dta", replace drop if area_id==16 | area_id==20 | area_id==25 *drop if area_id==6 | area_id==7 drop if area_id==2 | area_id==5 | area_id==19 | area_id==24 | area_id==27