clear all

* User set local path
loc path "C:\Dropbox\Dropbox\Ziss_Lake\Contesting_TA\Lobby_Data\finalTable1" 

cd "`path'"

* Import firm-level lobbying data
use "d_all19992017_v2.dta", clear

* Clean data
	drop if period=="UNDETERMINED"
	gen quarter=4
	replace quarter=3 if period=="3rd Quarter (July 1 - Sep 30)"
	replace quarter=2 if period=="2nd Quarter (Apr 1 - June 30)"
	replace quarter=2 if period=="Mid-Year (Jan 1 - Jun 30)"
	replace quarter=1 if period=="1st Quarter (Jan 1 - Mar 31)"
	gen t=yq(year, quarter)
	format t %tq

* Collapse to FTA-level lobbying data
	collapse (sum) amount* lob*, by(t)

* Label variables	
	foreach p in aus bah cafta chi col jor kor mor oma pan per sin cos dom els hon gua nic {
		lab var amount`p'1 "Specific country, all issues"
		lab var amount`p'2 "Specific country, trade issues"
		lab var amount`p'3 "Specific FTA bills"
		lab var amount`p'4 "Specific country, trade, and FTA bills"
		lab var lob`p'1 "Reports (specific country, all issues)"
		lab var lob`p'2 "Reports (specific country, trade issues)"
		lab var lob`p'3 "Reports (specific FTA bills)"
		lab var lob`p'4 "Reports (specific country, trade, and FTA bills)"
		}

* Rescale lobbying values
	forvalues t=1/4 {
	foreach p in aus bah cafta chi col jor kor mor oma pan per sin cos dom els hon gua nic {
		replace amount`p'`t'=amount`p'`t'*1000									// set values to millions, not billions as before
		}
		}

* Date variable
	gen y = yofd(dofq(t))
	lab var t "Date"

* Clean up
keep y t amountaus* amountbah* amountcafta* amountcol* amountjor* amountkor* amountmor* amountoma* amountpan* amountper* amountsin* amountchi*
drop *1 *2
order y t amountaus* amountbah* amountcafta* amountcol* amountjor* amountkor* amountmor* amountoma* amountpan* amountper* amountsin* amountchi*


save "lobbyingDataFTAs.dta", replace