topic How to join three tables using proc sql in SAS Procedures

How to join three tables using proc sql

SeanZ — Tue, 16 Jul 2013 08:19:17 GMT

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.

Re: How to join three tables using proc sql

Keith — Tue, 16 Jul 2013 12:01:38 GMT

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

full join

on a.id=b.id

full join

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.

Re: How to join three tables using proc sql

RichardinOz — Tue, 16 Jul 2013 12:08:33 GMT

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

Re: How to join three tables using proc sql

Amir — Tue, 16 Jul 2013 12:26:04 GMT

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.

Re: How to join three tables using proc sql

SeanZ — Tue, 16 Jul 2013 19:38:10 GMT

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