event window

[trich12]

I have a file that contains merger events and monthly stock closing prices for several years around the event.  I would like to extract the monthly closing prices for two specific months, month -14 before the event and -3 before the event.  The variables are:

merger date = __Date_Announced

identifier = gvkey

monthly closing price = PRCCM

date for prices = datadate (which is in format yyyymmdd)

I think I will need to extract the month from datadate, then get the prices for the -14 month and -3 month for each merger event. I just don't know the code to do this.  Any help would be so appreciated! Let me know if I need to provide any more information.

Thanks!

[Patrick]

You get normally the best answers if you provide a data step which creates some sample data and then you show us how the expected result should look like.

[trich12]

Thanks Patrick!  I just attached 2 files with a few observations from my data.  The file eventex contains the firm id (gvkey) and the date of the merger.  The file prcex contains the firm id (gvkey) and the date and price for each month.

My ideal result would be this for the first observation:

gvkey    __Date_Announced      -14monthdate      -14monthprice   -3monthdate   -3monthdate

1034          26JUL2004              20030531                  21.02            20040430        21.73

I don't know if it's possible to do this with these two files, but thank you in advance if you can help at all!

[Patrick · Accepted solution]

If your table PRCEX fits into memory then below code could work for you.

proc sql;
  create view V_eventex as
  select    
    gvkey ,
    __date_announced as Merger_Date format=date9.
  from <libref>.eventex
  ;
  create view V_PRCEX as
  select    
    gvkey,
    input(put(datadate,8.),yymmdd8.) as Data_Date format=date9.,
    prccm as Closing_Price
  from <libref>.PRCEX
  ;
quit;

data want(drop=_rc Data_Date);
  set V_eventex;

  if _n_=1 then
    do;
      if 0 then set V_PRCEX;
      declare hash h0 (dataset:'V_PRCEX');
      _rc=h0.defineKey('gvkey','Data_Date');
      _rc=h0.defineData('Closing_Price');
      _rc=h0.defineDone();
    end;

  attrib
    Month_3 format=date9.
    Price_3  format=best32.2
    Month_14 format=date9.
    Price_14 format=best32.2
  ;
  Data_Date=intnx('month',Merger_Date,-3,'e');

  if h0.find()=0 then
    do;
      Month_3=Data_Date;
      Price_3=Closing_Price;
    end;

  Data_Date=intnx('month',Merger_Date,-14,'e');

  if h0.find()=0 then
    do;
      Month_14=Data_Date;
      Price_14=Closing_Price;
    end;

  output;
run;

[trich12]

This was perfect. Thank you!