How to join three tables using proc sql

[SeanZ]

Hi,

I have a silly question. I want to full join three tables using proc sql.

I tried the following, but doesn't work. I wonder what's wrong with the code.

proc sql;

     create table new as

     select *

     from a full join b full join c

     on a.id = b.id = c.id;

quit;

Thanks.

[Keith · Accepted solution]

Do the tables all have the same structure?  This will make a difference as to which approach to take, but if ID is the only common variable then the code you need is :

proc sql;

create table new as select *

from

  a

  full join

  b

  on a.id=b.id

  full join

  c

  on b.id=c.id

;

quit;

You will get 2 warning messages saying that variable id already exists, due to the lazy approach of using select *.  You'll get the same message for any other variables that exist in more than one dataset, in which case you should explicitly list the variables and table source in the select statement.

[RichardinOz]

In a case like this you should provide the log.

When you say it does not work - do you get an error, or just not what you expected?

And stab in the dark - are a.id, b.id and c.id all of the same type (numeric or character)?

Richard

[SeanZ]

Yes. The log said missing an "ON" and statement will be ignored.

[Amir]

Hi,

In answer to your question "what's wrong with the code", looking at the documentation for joining tables in SQL:

SAS(R) 9.3 SQL Procedure User's Guide

It looks like the full join is for two tables:

"A full outer join, specified with the keywords FULL JOIN and ON, has all the rows from the Cartesian product of the two tables for which the SQL expression is true, plus rows from each table that do not match any row in the other table."

You will need to modify your approach if you want to join 3 tables.

Regards,

Amir.