[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

From   "Martin Weiss" <>
To   <>
Subject   Re: RE: st: RE: Programming a loop to exclude M&As in the 5 years prior to an M&A
Date   Thu, 16 Oct 2008 21:57:47 +0200

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...

----- Original Message ----- From: <>
To: <>
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.


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-----
Gesendet: Do. 16.10.08 (20:19)
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
within a column, but only within rows, I found out that the
tag target_cusip, generate(dupliucate_target)" command might be
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.
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,

Duplicates in terms of target_cusip
generate() must specify new variable

It works fine except that also duplicated observations are assigned
indicator if the consecutivly ordered year_of_announcement have a gap
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
new variable r(110);" even though the indicator variable did not
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
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
in just one column with the "duplicates tag cusip" command.

Thanks for your help and kind regards


> -----Ursprüngliche Nachricht-----
> Von: "Martin Weiss" <>
> Gesendet: Do. 16.10.08 (13:21)
> An:
> 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
> ( Also check the contents of
> Stata
> Journal archive, part of which is free:
> Martin
> -----Original Message-----
> From:
> [] On Behalf Of
> Sent: Thursday, October 16, 2008 12:03 PM
> To:
> Subject: st: Programming a loop to exclude M&As in the 5 years
> to an
> M&A
> Hello Statausers,
> I am new to STATA and try to program a screening loop to exclude
> 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 &
> 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 &
> 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 &
> 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 &
> 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 &
> 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
> year_of_announcement {.....}
> Kind regards
> Pascal Stock
> *
> *   For searches and help try:
> *
> *
> *
> *
> *   For searches and help try:
> *
> *
> *
> -----Ursprüngliche Nachricht Ende-----

#adBox3 {display:none;}

*   For searches and help try:

-----Ursprüngliche Nachricht Ende-----

Gratis: Jeden Monat 3 SMS versenden-
Mit freenetMail - Ihrer

*   For searches and help try:

*   For searches and help try:

© Copyright 1996–2023 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index