Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

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

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.

Attachment
Attachment

Accepted Solutions
Solution
‎02-09-2013 08:48 PM
Respected Advisor
Posts: 4,173

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,931

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

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

PG
Contributor
Posts: 36

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

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

Respected Advisor
Posts: 4,173

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

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?

Contributor
Posts: 36

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

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

Solution
‎02-09-2013 08:48 PM
Respected Advisor
Posts: 4,173

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

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;

Contributor
Posts: 36

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

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

Respected Advisor
Posts: 4,931

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

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

PG
Contributor
Posts: 36

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

Patrick and PGStats,

Thank you for your help.

Anna

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 214 views
  • 6 likes
  • 3 in conversation