Dear Mr. Weiss and all helpful STATA Users,
I figured ot how to get the target_cusip and acquiror_cusip in one
column by doubling the observation of each deal, and then creating the
following variable t_and_a_cusip that includes for the first [_n==1]
observation per deal the target_cusip and for the second [_n==2]
observation per deal the acquiror_cusip. Then I identified those
t_and_a_cusips, of the targets' and acquiror's, that are duplicated with
the "duplicates tag t_and_a_cusip" command.
sort deal_number
expand 2
generate t_and_a_cusip=""
by deal_number: replace t_and_a_cusip=target_cusip if [_n==1]
by deal_number: replace t_and_a_cusip=acquiror_cusip if [_n==2]
sort t_and_a_cusip year_of_announcement
duplicates tag t_and_a_cusip, generate(duplicated_cusip)
Now I have to figure out how to specify the range that the duplication
tag is assigned only when the difference between the last observation
and the current observation is less than 5 years. Henc I sorted the data
forst by the t_and_a_cusip, and then by the year_of_announcement.
e.g.
year_of_announcement t_and_a_cusip
1999 00001Q
2000 00001Q
2000 00001Q
2003 00001Q
2006 00001Q
Here the consecutive annual differences are less than 5 years, so a
duplication tag=1 is assigned. IHowever, if the differences would be
greater than five years like
year_of_announcement t_and_a_cusip duplication_tag
1992 00001Q 0
1999 00001Q 0
2000 00001Q 1
2006 00001Q 0
the obs with 2000 gets a tag as the previous M&A involving the company
with the cusip 00001Q as a target or acquiror occured less then five
years ago. This observation/deal I want to exclude to avoid within 5
years the problem of overlapping M&A deals. How to specifiy such a
qualifier ?
Kind regards
Pascal Stock
> -----Ursprüngliche Nachricht-----
> Von: [email protected]
> Gesendet: Do. 16.10.08 (20:19)
> An: [email protected]
> Betreff: RE: st: RE: Programming a loop to exclude M&As in the 5 years
prior to an M&A
>
> Dear Mr. Weiss,
>
> thanks for your quick reply. I looked for a solution with an indiator
> variable to signal that the target_cusips occur more than once within
> the last five years. As STATA cannot compare the values of
> target_cusip
> within a column, but only within rows, I found out that the
> "duplicate
> tag target_cusip, generate(dupliucate_target)" command might be
> usefull.
> If more than one observation with the same target_cusip occurs, it is
> assigned a number how often it occurs (1,2,3,...) in the generated
> inficator variable "duplicate_target". This is a convenient solution.
> My
> problem is now that I want to look for duplicates only within the
> last 5
> years, so I programmed the following loop:
>
> sort target_cusip year_of_announcement
> local j=1992
> forvalues j=1992/2006 {
>
> duplicates tag target_cusip if `j-5'<=year_of_announcement |
> `j-4'<=year_of_announcement | `j-3'<=year_of_announcement |
> `j-2'<=year_of_announcement | `j-1'<=year_of_announcement,
> generate(duplicated_target)
> }
>
> Duplicates in terms of target_cusip
> generate() must specify new variable
> r(110);
>
> It works fine except that also duplicated observations are assigned
> an
> indicator if the consecutivly ordered year_of_announcement have a gap
> of
> more than 5 years as well, which I do not want. Also sometimes no
> indicator (0=no duplication, 1,2,3 for multiple duplications) is
> assigned with "." as I get the error message "generate() must
> specify
> new variable r(110);" even though the indicator variable did not
> exist
> before in the dataset.
>
> The next problem is to figure out how to search for duplicated cusips
> between two different colums, the acquiror_cusip and the
> target_cusip.
> Is it possible to put the cusips in one column by duplicating the
> observations to have two otherwise identical observations that differ
> only in the cusip's stored in the same column, the pervious
> acquiror_cusip and target_cusip? This way I could search for the
> cusips
> in just one column with the "duplicates tag cusip" command.
>
> Thanks for your help and kind regards
>
> Pascal
>
>
> > -----Ursprüngliche Nachricht-----
> > Von: "Martin Weiss" <[email protected]>
> > Gesendet: Do. 16.10.08 (13:21)
> > An: [email protected]
> > Betreff: st: RE: Programming a loop to exclude M&As in the 5 years
> prior to an M&A
> >
> > Not fully understanding your prob, let me comment on two aspects:
> > Firstly,
> > it is not clear to me why you include the first line and set -local
> > j- equal
> > to 1987. The next line tells Stata exactly which values -j- should
> > acquire
> > in the loop as in
> >
> > **********
> > loc i=1
> > forv i=3/5{
> > di `i'
> > }
> > *********
> >
> > The first line is harmless, but also redundant.
> >
> > Secondly, it is not a good idea to -drop- observations left and
> > right.
> > Instead, create an indicator variable for your -if- qualifiers.
> > Simply do
> > something along these lines in your loop:
> >
> > ************
> > g indicator=0
> > replace indicator=1 if acquiror_cusip==acquiror_cusip &
> > year_of_announcement==`j'-1
> > ************
> >
> > Later you can condition your analyses on this indicator, but can go
> > back to
> > the original data if needed.
> >
> >
> > BTW, the FAQ on such problems are quite comprehensive and helpful
> > (http://www.stata.com/support/faqs/). Also check the contents of
> the
> > Stata
> > Journal archive, part of which is free:
> > http://www.stata-journal.com/archives.html
> >
> >
> > HTH
> > Martin
> >
> > -----Original Message-----
> > From: [email protected]
> > [mailto:[email protected]] On Behalf Of
> > [email protected]
> > Sent: Thursday, October 16, 2008 12:03 PM
> > To: [email protected]
> > Subject: st: Programming a loop to exclude M&As in the 5 years
> prior
> > to an
> > M&A
> >
> > Hello Statausers,
> >
> > I am new to STATA and try to program a screening loop to exclude
> from
> > my SDC M&A sample those M&As in which the acquiror or target of an
> > M&A
> > in a particular year_of_announcement==`j' with j=1987-2006 has been
> > the target or acquiror in an M&A in one of the preceeding 5 years.
> > This screening is needed to avoid the problem of overlapping events
> > in
> > the analysis of the stock returns after the M&A.
> >
> > My problem is how to program that STATA is supposed to look in a
> > particular year for all acquirors and targets that are acquirors or
> > targets in one of the past 5 years. The target_cusip and
> > acquiror_cusip as identifiers are organized in seperate colums. So
> > STATA has to go through these two colums in each year and look for
> > duplicated cusips that occur already in the year_of_announcement
> > looked at.
> >
> > So far I programmed the following loop, but do not know how to
> > indicate that the year_of_announcement==`j' is the base year from
> > which Stata has to go back year by year to look for the cusips
> > occuring in year j that are duplicated in the previous years :
> >
> > local j=1987
> > forvalues j=1987/2006 {
> >
> > drop if acquiror_cusip==acquiror_cusip &
> year_of_announcement==`j'-1
> > drop if acquiror_cusip==target_cusip & year_of_announcement==`j'-1
> > drop if target_cusip==target_cusip & year_of_announcement==`j'-1
> > drop if target_cusip==acquiror_cusip & year_of_announcement==`j'-1
> >
> > drop if acquiror_cusip==acquiror_cusip &
> year_of_announcement==`j'-2
> > drop if acquiror_cusip==target_cusip & year_of_announcement==`j'-2
> > drop if target_cusip==target_cusip & year_of_announcement==`j'-2
> > drop if target_cusip==acquiror_cusip & year_of_announcement==`j'-2
> >
> > drop if acquiror_cusip==acquiror_cusip &
> year_of_announcement==`j'-3
> > drop if acquiror_cusip==target_cusip & year_of_announcement==`j'-3
> > drop if target_cusip==target_cusip & year_of_announcement==`j'-3
> > drop if target_cusip==acquiror_cusip & year_of_announcement==`j'-3
> >
> > drop if acquiror_cusip==acquiror_cusip &
> year_of_announcement==`j'-4
> > drop if acquiror_cusip==target_cusip & year_of_announcement==`j'-4
> > drop if target_cusip==target_cusip & year_of_announcement==`j'-4
> > drop if target_cusip==acquiror_cusip & year_of_announcement==`j'-4
> >
> > drop if acquiror_cusip==acquiror_cusip &
> year_of_announcement==`j'-5
> > drop if acquiror_cusip==target_cusip & year_of_announcement==`j'-5
> > drop if target_cusip==target_cusip & year_of_announcement==`j'-5
> > drop if target_cusip==acquiror_cusip & year_of_announcement==`j'-5
> > }
> >
> > I gues a command with a varlist like forvalues j=1987/2006 of
> varlist
> > year_of_announcement {.....}
> >
> > Kind regards
> >
> > Pascal Stock
> >
> > *
> > * For searches and help try:
> > * http://www.stata.com/help.cgi?search
> > * http://www.stata.com/support/statalist/faq
> > * http://www.ats.ucla.edu/stat/stata/
> >
> > *
> > * For searches and help try:
> > * http://www.stata.com/help.cgi?search
> > * http://www.stata.com/support/statalist/faq
> > * http://www.ats.ucla.edu/stat/stata/
> >
> >
> >
> > -----Ursprüngliche Nachricht Ende-----
>
>
>
>
>
>
>
>
> #adBox3 {display:none;}
>
>
>
>
>
>
> *
> * For searches and help try:
> * http://www.stata.com/help.cgi?search
> * http://www.stata.com/support/statalist/faq
> * http://www.ats.ucla.edu/stat/stata/
>
>
>
> -----Ursprüngliche Nachricht Ende-----
Gratis: Jeden Monat 3 SMS versenden-
Mit freenetMail - Ihrer
kostenlose
E-Mail-Adresse
http://email.freenet.de/dienste/emailoffice/produktuebersicht/basic/sms/index.html?pid=6830
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
