Dear all,
I am trying to generate a sum of all transaction values (ValueofTransactionmil) for each advisor (TargetPrimaryAdvisor) in each year (YearAnnounced), my data set contains ca. 20,000 observations over 28 years with (sometimes) multiple observations per advisor, an example of my data is given below:
I have tried to calculate the sum variable in the following way:
This way gives an error after the first TargetAdvisorID because egen does not allow for any kind of replace. Does anyone have a suggestion on solving this issue?
Stijn
I am trying to generate a sum of all transaction values (ValueofTransactionmil) for each advisor (TargetPrimaryAdvisor) in each year (YearAnnounced), my data set contains ca. 20,000 observations over 28 years with (sometimes) multiple observations per advisor, an example of my data is given below:
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double ValueofTransactionmil str30 TargetPrimaryAdvisor float YearAnnounced
48.888 "ABN AMRO Bank NV" 1991
43.607 "Alex Brown & Sons Inc" 1991
583.402 "Alfred Berg A/S" 1991
94.88 "BBV Corporate Finance" 1991
54.317 "Banco Bilbao Vizcaya SA" 1991
28.442 "Barclays de Zoete Wedd Ltd" 1991
61.1 "Baring Brothers & Co Ltd" 1991
138.163 "Baring Brothers & Co Ltd" 1991
50 "Bear Stearns & Co Inc" 1991
35 "Bruce Barnes Associates" 1991
82.5 "CE Unterberg Towbin" 1991
103.346 "Hambros Bank Ltd" 1991
50.775 "Hoare Govett Ltd" 1991
160.1 "Houlihan Dorton Jones" 1991
88.438 "IBI Corporate Finance" 1991
72.219 "J Henry Schroder Wagg & Co Ltd" 1991
199.574 "JO Hambro Magan & Co Ltd" 1991
500 "James D Wolfensohn Inc" 1991
134.971 "KPMG Peat Marwick LLP" 1991
71.9 "Kidder Peabody & Co Inc" 1991
331.178 "Kleinwort Benson Ltd" 1991
24.9 "Ladenburg Thalmann & Co" 1991
105 "Lazard Freres & Co LLC" 1991
269.3 "Merrill Lynch Capital Markets" 1991
520 "Montgomery Securities" 1991
24.5 "Montgomery Securities" 1991
2079.729 "Morgan Grenfell & Co Ltd" 1991
20.9 "Morgan Grenfell & Co Ltd" 1991
end
Code:
// Calculate one year deal value for each advisor
gen TargetAdvisorSum=.
egen TargetAdvisorID = group(TargetPrimaryAdvisor)
levelsof TargetAdvisorID, local(taradvlist)
set trace on
quietly foreach n in `taradvlist'{
bysort YearAnnounced: egen TargetAdvisorValue1=total(ValueofTransactionmil)
replace TargetAdvisorSum=TargetAdvisorValue1 if `n'==TargetAdvisorID
}
// Calculate three year deal value
quietly by TargetPrimaryAdvisor YearAnnounced: gen dup = cond(_N==1,0,_n)
drop if dup>1
drop dup
xtset TargetPrimaryAdvisor YearAnnounced
gen TargetAdvisorSum3=TargetAdvisorSum+l.TargetAdvisorSum+l2.TargetAdvisorSum
Stijn
Comment