Re: RE: st: RE: Programming a loop to exclude M&As in the 5 years prior to an M&A

["Martin Weiss" <martin.weiss1@gmx.de>]

Is it at all possible that you frame your problem in a more general form, 
like in terms of a dataset shipped with Stata? I find it terribly hard to 
understand your data structure. You definitely get more replies with a 
built-in dataset which users can play around with. At the same time, that 
forces you to think about the core of your problem...

HTH
Martin
_______________________
----- Original Message ----- 
From: <pascalstock@freenet.de>
To: <statalist@hsphsun2.harvard.edu>
Sent: Thursday, October 16, 2008 9:42 PM
Subject: RE: RE: st: RE: Programming a loop to exclude M&As in the 5 years 
prior to an M&A


> 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: pascalstock@freenet.de
> > Gesendet: Do. 16.10.08 (20:19)
> > An: statalist@hsphsun2.harvard.edu
> > 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" <martin.weiss1@gmx.de>
> > > Gesendet: Do. 16.10.08 (13:21)
> > > An: statalist@hsphsun2.harvard.edu
> > > 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: owner-statalist@hsphsun2.harvard.edu
> > > [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of
> > > pstock@rumms.uni-mannheim.de
> > > Sent: Thursday, October 16, 2008 12:03 PM
> > > To: statalist@hsphsun2.harvard.edu
> > > 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/


*
*   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/