LEFT JOIN in SAS using PROC SQL

[SasHelpNeeded]

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.

[RW9 · Accepted solution]

Hi,

If you want all observations from either than do: 

proc sql;
     create table want as
     select     COALESCE(FIRST_TABLE.ID,SECOND_TABLE.ID) as ID,
                    ...
     from       FIRST_TABLE
     full join     SECOND_TABLE
     on          FIRST_TABLE.ID=SECOND_TABLE.ID;
quit;
 

The full join means all rows from both tables are merged on id giving full list, you do need the coalesce to get id populated, as if you just take first table then it would be missing for those which appear only in second table.

Another approach from @PGStats:

You should use a CROSS JOIN to get all combinations of ID and trading_date and then LEFT JOIN to your trading data :

proc sql;
create table joined as
select I.ID, a.trading_date as date, b.price
from
  (select unique ID from b) as I cross join
  a left join
  b on I.ID=b.ID and a.trading_date=b.date
order by ID, date;
quit;

[PGStats]

You should use a CROSS JOIN to get all combinations of ID and trading_date and then LEFT JOIN to your trading data :

proc sql;

create table joined as

select I.ID, a.trading_date as date, b.price

from

  (select unique ID from b) as I cross join

  a left join

  b on I.ID=b.ID and a.trading_date=b.date

order by ID, date;

quit;

PG