clear
set mem 100m

use c:\ar_sponsorship.dta
egen nameyr1=concat(pname pyear pstatus)
sort nameyr1
drop if pstatus=="P"
drop if pyear>1993
drop if pyear<1993
reshape long g, i(nameyr) j(billnum1)
drop billnum
rename g bill1
drop if bill1==.
tempfile bills1
save bills1, replace

use c:\ar_sponsorship.dta
egen nameyr2=concat(pname pyear pstatus)
sort nameyr2
drop if pstatus=="P"
drop if pyear>1993
drop if pyear<1993
reshape long g, i(nameyr2) j(billnum2)
drop billnum
rename g bill2
drop if bill2==.
tempfile bills2
save bills2, replace

insheet using C:\shell.txt, names clear
tempfile shellfile
save shellfile, replace

************* merge

use bills1, clear
sort bill1
generate bill=bill1

save bills1, replace
use bills2, clear
sort bill2

generate bill=bill2
joinby bill using bills1, unmatched(both)
drop _merge
drop if nameyr1==nameyr2

egen number1=group(nameyr1)
egen number2=group(nameyr2)
egen pairname=concat(nameyr1 nameyr2)
save matrix_ar, replace

collapse number1, by (nameyr1)
drop if number1==.
sort number1
save numfile1, replace
list number1 nameyr1

use matrix_ar, clear
collapse number2, by(nameyr2)
drop if number2==.
sort number2
save numfile2, replace
list number2 nameyr2

use matrix_ar, clear
generate pairid=(number1*1000)+number2
sort pairid
egen totpair=sum(bill>0 & bill<9999), by(pairid)
joinby pairid using shellfile, unmatched(using)

drop if number2==.
drop if number1==.

collapse number1 number2 totpair, by(pairid)

keep number1 number2 totpair
sort number1 number2
reshape wide totpair, i(number1) j(number2)
foreach var of varlist totpair1-totpair100 {
 replace `var'=0 if `var'==.
}

sort number1
merge number1 using numfile1
drop number1
save matrix_ar, replace

outfile using C:\cospfreq.txt, wide replace c m