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.
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.
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.
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
Yes. The log said missing an "ON" and statement will be ignored.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.