topic Re: Need help on sql: meger two databases for a special case in SAS Procedures

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

Anna_Guo — Sat, 09 Feb 2013 23:27:37 GMT

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.

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

PGStats — Sat, 09 Feb 2013 23:39:42 GMT

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

and a.rating_date=b.date

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

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

Anna_Guo — Sat, 09 Feb 2013 23:55:38 GMT

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

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

Patrick — Sun, 10 Feb 2013 01:15:57 GMT

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?

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

Anna_Guo — Sun, 10 Feb 2013 01:22:27 GMT

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

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

Patrick — Sun, 10 Feb 2013 01:48:02 GMT

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;

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

Anna_Guo — Sun, 10 Feb 2013 02:04:22 GMT

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

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

PGStats — Sun, 10 Feb 2013 02:17:02 GMT

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

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

Anna_Guo — Sun, 10 Feb 2013 02:58:31 GMT

Patrick and PGStats,

Thank you for your help.

Anna