BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SeanZ
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Keith
Obsidian | Level 7

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

4 REPLIES 4
Keith
Obsidian | Level 7

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
Quartz | Level 8

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
Obsidian | Level 7

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

Amir
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 47850 views
  • 5 likes
  • 4 in conversation