I wrote some code to quickly load the latest state and international COVID-19 data into Stata. The U.S. data comes from covidtracking.com and the international data comes ecdc.europa.eu. These data are updated continuously, so each time the code is executed, it should load-in the latest available data.
Here's a link to download the do file from Google Drive:
https://drive.google.com/file/d/18RIpar2OAGaQyxsGrLOz_PBxoaUMzskw/view?usp=sharing
https://drive.google.com/file/d/1SLFdySD-6-fv9bzb0Rf731dJy9-LzuIa/view?usp=sharing
Alternatively, I've pasted it below. I hope it's helpful. Please let me know if you see any issues with it or have any questions:
Edit: As suggested by u/NotMitchelBade**, I converted the geoid variable for countries to 3-letters rather than 2-letters. States are still 2-letters. This way geoid uniquely identifies a geography. I also added a population variable which I had intended to include originally, but forgot. The original code is still available in the crossed-out link above.**
*covid19_data_20200327
*Loads daily U.S. State and International COVID-19 data into Stata
*Intermediate file list
loc file_list count_codes state_abbrv state_popul state_covid intnl_covid
*Paths
loc csv_count_codes "https://docs.google.com/spreadsheets/d/1qr1kqW62DRlaLiGdnmJqmLfPcVdy0fVmZaAh7Tat-uk/gviz/tq?tqx=out:csv&sheet=codes"
loc csv_state_abbrv "https://docs.google.com/spreadsheets/d/1kuniBcV-3qMjQRH7nqTS9CXSapPFCSO5YACP0Y2kvmU/gviz/tq?tqx=out:csv&sheet=state_abbreviations"
loc csv_state_popul "https://docs.google.com/spreadsheets/d/1kuniBcV-3qMjQRH7nqTS9CXSapPFCSO5YACP0Y2kvmU/gviz/tq?tqx=out:csv&sheet=cleaned"
loc csv_state_covid "http://covidtracking.com/api/states/daily.csv"
loc csv_intnl_covid "https://www.ecdc.europa.eu/sites/default/files/documents/COVID-19-geographic-disbtribution-worldwide-2020-03-25_0.csv"
*Load CSV files into Stata
tempfile tmp_csv `file_list'
foreach x of local file_list {
copy "`csv_`x''" `tmp_csv', replace
import delimited using `tmp_csv', clear varnames(1)
save ``x'', replace //Add additional macro quotes
}
*Combining and data
use `state_covid', clear
***Merging in state population and state name
merge m:1 state using `state_popul', assert(match) nogen
merge m:1 state using `state_abbrv', assert(match) nogen
***Appending with international data
label define note 1 "U.S. state data from covidtracking.com" ///
2 "International data from ecdc.europa.eu"
gen note=1
append using `intnl_covid'
replace note=2 if note==.
label values note note
*Cleaning
gen _geoid =cond(note==1, state,geoid)
gen _name =cond(note==1, state_name,countriesandterritories)
gen _date =cond(note==1, date(string(date,"%15.0f"),"YMD"), ///
date(daterep,"DMY") )
gen _new_cases =cond(note==1, positiveincrease,cases)
gen _new_death =cond(note==1, deathincrease,deaths)
gen _new_hospt =hospitalizedincrease //Only available in state data
gen _new_tests =totaltestresultsincrease //Only available in state data
gen _pop_count =cond(note==1, population,pop_data2018)
keep _* note
rename _* *
format date %td
format pop_count %15.0fc
replace name=upper(subinstr(name," ","_",.))
order note, last
compress
*03/27/20 EDIT: Updating geoid to 3 characters for international data
merge m:1 geoid using "`count_codes'", keep(1 3)
assert _merge==3 if note==2
replace geoid=geoid3 if note==2
drop country geoid3 code _merge
isid geoid date
sort note geoid date
*Adding notes
note: original data downloaded from the covidtrackng and European Centre for Disease Prevention and Control websites"
note: data current as of `=c(current_date)'
note: http://covidtracking.com/api/states/daily.csv
note: https://www.ecdc.europa.eu/sites/default/files/documents/COVID-19-geographic-disbtribution-worldwide-2020-03-25_0.csv