Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: [Stata 8] Merge question


From   "Eric G. Wruck" <ewruck@econalytics.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: [Stata 8] Merge question
Date   Sat, 7 May 2005 11:10:14 -0400

Hi Cameron:

First of all, I want to compliment you on how well you laid out your problem.  It makes me want to help you. 

Anyway, I came up with a different approach that I think would handle multiple & overlapping events.  It creates a column or variable for each event that gives the number of trading days relative to the event.  This way you can change your windows easily.  I think the .do file could be cleaned up & made more efficient but I do think it works.  Voila...


Eric

============================================================================ 

. /*  event_dates.do                                          */
.
. version 9.0

.
. clear

.
. use http://www-personal.umich.edu/~chooper/stata/returns

.
. sort permno ticker date

.
. save temp1, replace
file temp1.dta saved

.
. use http://www-personal.umich.edu/~chooper/stata/event_dates, clear

.
. sort permno ticker date

.
. merge permno ticker date using temp1

.
. keep if _merge == 2 | _merge == 3
(0 observations deleted)

.
. sort permno ticker date

.
. by permno ticker: gen no = _n

.
. save temp2, replace
file temp2.dta saved

.
. keep if _merge == 3
(496 observations deleted)

.
. by permno: gen evt = _n

.
. keep permno no evt

.
. reshape wide no, i(permno) j(evt)
(note: j = 1 2 3 4 5)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                        8   ->       2
Number of variables                   3   ->       6
j variable (5 values)               evt   ->   (dropped)
xij variables:
                                     no   ->   no1 no2 ... no5
-----------------------------------------------------------------------------

.
. save temp3, replace
file temp3.dta saved

.
. use temp2, clear

.
.   drop _merge

.  
. merge permno using temp3
variable permno does not uniquely identify observations in the master data

.
. rename no n

.
. foreach var of varlist no* {
  2.   replace `var' = n - `var'
  3.   }
(502 real changes made)
(502 real changes made)
(503 real changes made)
(252 real changes made)
(252 real changes made)

.
.
. drop _merge

.
. list in 1/15

     +-------------------------------------------------------------------------------+
     | permno   ticker        date         ret    n   no1   no2    no3    no4    no5 |
     |-------------------------------------------------------------------------------|
  1. |  10107     MSFT   03 Jan 00    -.001606    1    -1   -47   -119   -179   -250 |
  2. |  10107     MSFT   04 Jan 00   -.0337802    2     0   -46   -118   -178   -249 |
  3. |  10107     MSFT   05 Jan 00    .0105438    3     1   -45   -117   -177   -248 |
  4. |  10107     MSFT   06 Jan 00   -.0334981    4     2   -44   -116   -176   -247 |
  5. |  10107     MSFT   07 Jan 00    .0130682    5     3   -43   -115   -175   -246 |
     |-------------------------------------------------------------------------------|
  6. |  10107     MSFT   10 Jan 00    .0072911    6     4   -42   -114   -174   -245 |
  7. |  10107     MSFT   11 Jan 00   -.0256125    7     5   -41   -113   -173   -244 |
  8. |  10107     MSFT   12 Jan 00   -.0325714    8     6   -40   -112   -172   -243 |
  9. |  10107     MSFT   13 Jan 00    .0189014    9     7   -39   -111   -171   -242 |
 10. |  10107     MSFT   14 Jan 00    .0411594   10     8   -38   -110   -170   -241 |
     |-------------------------------------------------------------------------------|
 11. |  10107     MSFT   18 Jan 00    .0272829   11     9   -37   -109   -169   -240 |
 12. |  10107     MSFT   19 Jan 00   -.0720867   12    10   -36   -108   -168   -239 |
 13. |  10107     MSFT   20 Jan 00   -.0093458   13    11   -35   -107   -167   -238 |
 14. |  10107     MSFT   21 Jan 00   -.0212264   14    12   -34   -106   -166   -237 |
 15. |  10107     MSFT   24 Jan 00   -.0240964   15    13   -33   -105   -165   -236 |
     +-------------------------------------------------------------------------------+

.
===============================================================================


>Dear list,
>
>I want to merge event dates with a series of daily stock returns and subsequently define various event windows. What seemed like a straight forward problem is actually proving surprisingly difficult (for me anyway).
>
>I have made up some illustrative data. Suppose I have a file containing daily returns for a couple of firms like this:
>
>
>. version
>version 8.2
>. use http://www-personal.umich.edu/~chooper/stata/returns
>. list in 1/10
>
>     +-----------------------------------------+
>     | permno   ticker        date         ret |
>     |-----------------------------------------|
>  1. |  10107     MSFT   03 Jan 00    -.001606 |
>  2. |  10107     MSFT   04 Jan 00   -.0337802 |
>  3. |  10107     MSFT   05 Jan 00    .0105438 |
>  4. |  10107     MSFT   06 Jan 00   -.0334981 |
>  5. |  10107     MSFT   07 Jan 00    .0130682 |
>     |-----------------------------------------|
>  6. |  10107     MSFT   10 Jan 00    .0072911 |
>  7. |  10107     MSFT   11 Jan 00   -.0256125 |
>  8. |  10107     MSFT   12 Jan 00   -.0325714 |
>  9. |  10107     MSFT   13 Jan 00    .0189014 |
> 10. |  10107     MSFT   14 Jan 00    .0411594 |
>     +-----------------------------------------+
>
>I also have a file of event dates:
>
>. use http://www-personal.umich.edu/~chooper/stata/event_dates
>. list
>
>     +-----------------------------+
>     | permno   ticker        date |
>     |-----------------------------|
>  1. |  10107     MSFT   04 Jan 00 |
>  2. |  10107     MSFT   10 Mar 00 |
>  3. |  10107     MSFT   22 Jun 00 |
>  4. |  10107     MSFT   18 Sep 00 |
>  5. |  10107     MSFT   28 Dec 00 |
>     |-----------------------------|
>  6. |  12490      IBM   12 Jan 00 |
>  7. |  12490      IBM   07 Apr 00 |
>  8. |  12490      IBM   25 Jul 00 |
>     +-----------------------------+
>
>Merging is straightforward. However I also want to create a set of 3 event windows for each event date: pre-event, event, and post-event. Define the event date as 0 then
>
>pre-event = days -7 to -3
>event = days -2 to +2
>post-event = days +3 to + 7
>
>** These are trading dates not calendar dates (each observation is a trading date). **
>
>I thought this would be easy, but I just can't seem to make it happen. Please note that in practice I have a sample containing daily data for thousands of firms, many of which have over 10 years of data. I also have 4 event dates per firm-year. This means that the method shown at
>
>http://dss.princeton.edu/online_help/analysis/multiple_event_dates.htm
>
>which creates a duplicate set of observations for each event date / company combination is impractical. The resulting file is simply too large.
>
>Here is a mocked up file showing what I am aiming for. I have shown data around one event_date. The variable TYPE is defined as (0 = non-event, 1 = event, 2 = pre and 3 = post.
>
>. use http://www-personal.umich.edu/~chooper/stata/desired_result
>. list in 39/57
>
>    
>+-------------------------------------------------------------------+
>     | permno   ticker        date   event_d~e         ret   type  
>indx |
>    
>|-------------------------------------------------------------------|
> 39. |  10107     MSFT   28 Feb 00           .    .0027379      0     
>. |
> 40. |  10107     MSFT   29 Feb 00           .   -.0238908      0     
>. |
> 41. |  10107     MSFT   01 Mar 00           .    .0160839      2    
>-7 |
> 42. |  10107     MSFT   02 Mar 00           .    .0282175      2    
>-6 |
> 43. |  10107     MSFT   03 Mar 00           .    .0294511      2    
>-5 |
>    
>|-------------------------------------------------------------------|
> 44. |  10107     MSFT   06 Mar 00           .   -.0572172      2    
>-4 |
> 45. |  10107     MSFT   07 Mar 00           .    .0248276      2    
>-3 |
> 46. |  10107     MSFT   08 Mar 00           .    .0289367      1    
>-2 |
> 47. |  10107     MSFT   09 Mar 00           .    .0464356      1    
>-1 |
> 48. |  10107     MSFT   10 Mar 00   10 Mar 00         .01      1     
>0 |
>    
>|-------------------------------------------------------------------|
> 49. |  10107     MSFT   13 Mar 00           .    -.029703      1     
>1 |
> 50. |  10107     MSFT   14 Mar 00           .   -.0293367      1     
>2 |
> 51. |  10107     MSFT   15 Mar 00           .    .0026281      3     
>3 |
> 52. |  10107     MSFT   16 Mar 00           .           0      3     
>4 |
> 53. |  10107     MSFT   17 Mar 00           .    .0419397      3     
>5 |
>    
>|-------------------------------------------------------------------|
> 54. |  10107     MSFT   20 Mar 00           .   -.0201258      3     
>6 |
> 55. |  10107     MSFT   21 Mar 00           .     .055199      3     
>7 |
> 56. |  10107     MSFT   22 Mar 00           .    .0048662      0     
>. |
> 57. |  10107     MSFT   23 Mar 00           .    .0835351      0     
>. |
>    
>+-------------------------------------------------------------------+
>
>Any suggestions greatly appreciated.
>
>Cameron
>
>*
>*   For searches and help try:
>*   http://www.stata.com/support/faqs/res/findit.html
>*   http://www.stata.com/support/statalist/faq
>*   http://www.ats.ucla.edu/stat/stata/


-- 

===================================================

       Eric G. Wruck
       Econalytics
       2535 Sherwood Road
       Columbus, OH  43209

       ph:      614.231.5034
       cell:    614.330.8846
       eFax:    614.573.6639
       eMail:   ewruck@econalytics.com
       website: http://www.econalytics.com

====================================================
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



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