Need help on sql: meger two databases for a special case

[Anna_Guo]

Hi, guys

I need your help again. I am trying to merge two database.One includes firms with bond downgradings, firm id and the date when they got downgraded. The other is a huge database with all North American firms' daily stock return .  I would like to add stock returns during a 7-day window( 3 trading days before and 3 trading days after the date of  the downgrade) for these downgraded firms to  table1 . Basicly, each observation on table1 will become 7 observatios.

If I only need the date of downgrade, I can use sql merger like this:

proc sql;

create table merged as select a.*, b.* from table1 as a left join table2 as b on a.lpermno=b.lpermno and a.rating_date=b.date;

Above procedures will only include one date. Also,  date on table2 is  not canlendar day, there may be missing dates due to the close of stock market on weekend or holiday so it is possible that the date of downgrading might occur on the date when the market was not traded (that is, a date is not on table 2, but in table 1).

I attached two tables. My actual table2  is as large as 2GB.

Thanks.

[PGStats]

Assuming that the dates are SAS dates (and not datetimes), you only need to replace

and a.rating_date=b.date

by

and b.date between a.rating_date-3 and a.rating_date+3

PG

[Anna_Guo]

PG Stats,

Thank you . But table2's dates are not continuous,many dates are missing on table2. How can I make sure I can generate 7 dates for each observation on table1?

I tried , most observations only generate 4 or five dates not 7 dates.

Anna

[Patrick]

You will need some kind of post processing to fix up your time series. Question is: What are the rules? What value should variable RET get for dates you need to make up?

[Anna_Guo]

Yes, I am thinking  of adding those missing dates first then asigning a missing value to the added date for RET. Anyone easy way to do it?

Thanks,

Anna

[Patrick · Accepted solution]

You said that the table with daily stock returns is huge so it's may be better to touch this table as little as possible. Under the assumption that there is only 1 downgrade per firm in a 7 days window below code should work:

data work.table1_extended;
  set work.table1;

  do date=rating_date-3 to rating_date+3;
    output;
  end;
run;

proc sql feedback;
  create table want as
    select a.rating_date format=date9.,
      a.lpermno    ,
      a.date format=date9.,
      b.ret       
    from   table1_extended a
      left outer join table2 b
        on (
        a.lpermno = b.lpermno and a.date=b.date
        )
      order by a.lpermno,a.rating_date, a.date;
quit;

[Anna_Guo]

Patrick,

Thank you very mcuh. Unfortunately it is very normal in my data that one firm has multiple downgrades. What changes should I make to your code to facilitate this situation?

Very much appreciate your time.

Anna

[PGStats]

Patrick's code will work even if there are multiple close downgrades. You will simply get the same daily stock return associated with many downgrades. - PG

[Anna_Guo]

Patrick and PGStats,

Thank you for your help.

Anna