Help using Base SAS procedures

How to join three tables using proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

How to join three tables using proc sql

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.


Accepted Solutions
Solution
‎07-16-2013 08:01 AM
Regular Contributor
Posts: 151

Re: How to join three tables using proc sql

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.

View solution in original post


All Replies
Solution
‎07-16-2013 08:01 AM
Regular Contributor
Posts: 151

Re: How to join three tables using proc sql

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.

Super Contributor
Posts: 644

Re: How to join three tables using proc sql

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

Frequent Contributor
Posts: 122

Re: How to join three tables using proc sql

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

Super Contributor
Posts: 282

Re: How to join three tables using proc sql

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 7526 views
  • 4 likes
  • 4 in conversation