Help using Base SAS procedures

LEFT JOIN in SAS using PROC SQL

Reply
Occasional Contributor
Posts: 5

LEFT JOIN in SAS using PROC SQL

0 down vote  favorite

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.

Super User
Super User
Posts: 7,401

Re: LEFT JOIN in SAS using PROC SQL

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.

Respected Advisor
Posts: 4,651

Re: LEFT JOIN in SAS using PROC SQL

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

PG
Ask a Question
Discussion stats
  • 2 replies
  • 3296 views
  • 0 likes
  • 3 in conversation