LEFT JOIN in SAS using PROC SQL

Question

I am new to SAS and have this basic problem. I have a list of NYSE trading dates in table A as follows -

trading_date
1st March 2012
2nd March 2012
3rd March 2012
4th March 2012
5th March 2012
6th March 2012

I have another table B that has share price information as -

Date          ID    Ret Price
1st March 2012  1   …   …
3rd March 2012  1   …   …
4th March 2012  1   …   …
5th March 2012  1   …   …
6th March 2012  1   …   …
1st March 2012  2   …   …
3rd March 2012  2   …   …
4th March 2012  2   …   …

... has numeric data related to price and returns.

Now I need to join the NYSE Data table to the above table to get the following table -

Date         ID    Ret  Price
1st March 2012  1   …   …
2nd March 2012  1   0   0
3rd March 2012  1   …   …
4th March 2012  1   …   …
5th March 2012  1   …   …
6th March 2012  1   …   …
1st March 2012  2   …   …
2nd March 2012  2   0   0
3rd March 2012  2   …   …
4th March 2012  2   …   …

i.e. a simple left join. The zero's will be filled with . in SAS to indicate missing values, but you get the idea. But if I use the following command -

proc sql;
create table joined as
select table_a.trading_date, table_b.* from table_a LEFT OUTER join table_b on table_a.trading_date=table_b.date;
quit;

The join happens only for the first ID (i.e. ID=1) while for the rest of the IDs, the same data is maintained. But I need to insert the trade dates for all IDs.

How can get the final data without running a do while loop for all IDs? I have 1000 IDs and looping and joining 1000 times is not an option due to limited memory.

Answer 1

Joe is right, you need to take also ID into consideration, but with his solution you cannot get 2nd March 2012 because no one is trading that day. You can do everything with just one sql step (which will take a bit longer):

proc sql;
   create table final as
   select d.trading_date, d.ID, t.Price, t.Ret
   from
   (
      select trading_date, ID 
      from table_a, (select distinct ID from table_b) 
   ) d
   left join
   (
      select *
      from table_b
   ) t
   on t.Date=d.trading_date and t.ID=d.ID
   order by d.id, d.trading_date;
quit;