clear all
set more off
cap log cl
* target firm as suppliers
use Revere201512.dta, clear
keep if rel_type=="SUPPLIER"
local vars "_cusip _name _ticker"
foreach var of local vars {
ren source`var' c`var'
ren target`var' s`var'
}
keep start_ end_ revenue_percent c_name c_ticker c_cusip s_name s_ticker s_cusip
save temp.dta, replace

* target firm as customers
use Revere201512.dta, clear
keep if rel_type=="CUSTOMER"
local vars "_cusip _name _ticker"
foreach var of local vars {
ren source`var' s`var'
ren target`var' c`var'
}
keep start_ end_ revenue_percent c_name c_ticker c_cusip s_name s_ticker s_cusip

append using temp.dta

* drop if cusip is missing
egen miss=rowmiss(s_cusip)
egen misss=rowmiss(c_cusip)
drop if miss | misss


drop miss misss

* replace end_date=31dec2015, if end_date==1jan4000
replace end_ = 20453 if end_==745095

* identify customer_supplier pairs
egen s_c_group=group(s_cusip c_cusip)
egen start_end_group=group(start_ end_)
drop if s_c_group==.
egen group=group(s_c_group start_end_group)
duplicates drop group, force

* convert year_month into standard index (april2003 as 0)
gen start_year=year(start_)
gen start_month=month(start_)
gen end_year=year(end_)
gen end_month=month(end_)
sort start_
gen start_index=12*(start_year-2003)+start_month-4
gen end_index=12*(end_year-2003)+end_month-4

* expand observations by their month duration
gen weight= end_index-start_index+1
expandcl weight, cl(group) gen(obs)

* anti-convert standard index into year_month
bys group: gen index=_n
replace index=start_index+index-1
gen year=floor((index+3)/12)+2003
gen month=index+4-12*(year-2003)

drop start_ end_ s_c_group start_end_group group start_year start_month end_year end_month start_index end_index weight obs index
replace s_cusip=substr(s_cusip,1,6)
replace c_cusip=substr(c_cusip,1,6)
duplicates drop s_cusip c_cusip year month, force

* quarterly

gen qtr =1 if month ==2 | month ==3 | month ==4
replace qtr =2 if month ==5 | month ==6 | month ==7
replace qtr =3 if month ==8 | month ==9 | month ==10
replace qtr =4 if month ==11 | month ==12 | month ==1
replace year = year - 1 if month ==1
gen datacqtr=year*10+qtr
drop revenue_percent year month qtr
duplicates drop s_cusip c_cusip datacqtr, force
compress

save Revere_quarterly.dta, replace

