clear all
set more off
#delimit;

/*************************************************************************************************************
This do-file reads in the BLS QCEW by industry data. 

1. Read in data 
*************************************************************************************************************/

do paths;
tempfile data_combined;
/*************************************************************************************************************
1. Flat file of level by industry 
*************************************************************************************************************/
forvalues yy=1990/2011 {;
	di `yy';
	local ftppath pub/special.requests/cew/beta/`yy'/`yy'.q1-q4.singlefile.zip;
	file open batfile using batfile.bat, write replace;
	file write batfile "user ftp" _n(2) "get `ftppath'" _n "quit" _n;
	file close batfile;
	!ftp -n -s:batfile.bat ftp.bls.gov; 
	local data = regexr("`ftppath'","^[a-zA-Z0-9/\.]+/","");
	local data = regexr("`data'","\.zip","");
	unzipfile `data'.zip;
	insheet using `data'.csv, comma names clear;
	qui erase `data'.zip;
	qui erase `data'.csv;
	qui keep if inrange(agglvl_code,74,74) & own_code==5; *Only keep county data at naics-sector level, private sector;
	capture qui append using `data_combined';
	qui save `data_combined', replace;
};
qui gen quarterly = yq(year,qtr);
qui format quarterly %tq;
qui save `data_combined', replace;

/*************************************************************************************************************
2. Documentation files
*************************************************************************************************************/
foreach var in agglvl industry ownership area {;
	file open batfile using batfile.bat, write replace;
	file write batfile "user ftp" _n(2) "get pub/special.requests/cew/beta/titles/`var'_titles.csv" _n "quit" _n;
	file close batfile;
	!ftp -n -s:batfile.bat ftp.bls.gov; 
	insheet using `var'_titles.csv, comma names clear;
	qui erase `var'_titles.csv;
	if "`var'"=="ownership" {;
		merge 1:m own_code using `data_combined', keep(matched using) nogenerate;
	};
	else if "`var'"=="area" {;
		merge 1:m area_fips using `data_combined', keep(matched using) nogenerate;
	};
	else {;
		merge 1:m `var'_code using `data_combined', keep(matched using) nogenerate;
	};
	qui save `data_combined', replace;
};


/*************************************************************************************************************
3. Save
*************************************************************************************************************/
qui sort quarter industry_code;
qui save "QCEW-county-naics-sector", replace;
qui zipfile "QCEW-county-naics-sector.dta", saving("$SavePath/QCEW-county-naics-sector", replace);
qui erase "QCEW-county-naics-sector.dta";