FULL JOIN with Multiple Datasets in ID variable (one record per ID)

[glcoolj12]

Hello,

I'm looking to fully join 7 datasets (set1-set7) together on variable 'ID' to create a master file that contains one record for each ID (no duplicate IDs).   The issue is that not all IDs are in every dataset (i.e. ID="333" may only be in set3 and set6).   I've been joining the sets piece by piece (see SAS code),  but I was curious if anyone knows a way to consolidate my code into one PROC SQL statement to achieve what I'm trying to do.  I've attempted myself but I get the SAS NOTE ("The execution of this query involves performing one or more Cartesian product joins that can not be optimized.")  Thanks!

proc sql;
create table test1 as 
select distinct coalesce(a.ID, b.ID) as ID, a.var1, b.var2
from 
(select ID, var1 from set1) as a
full join 
(select ID, var2 from set2) as b
on a.ID=b.ID;
quit;
 
proc sql;
create table test2 as 
select distinct coalesce (a.ID, b.ID) as ID, a.var1, a.var2, b.var3
from 
(select ID,var1,var2 from test1) as a
full join
(select ID, var2 from set3) as b
on a.ID=b.ID;
quit;


set 4...
set 5...
set 6...
set 7...

[ballardw · Accepted solution]

You might consider starting with a set constructed of union joins on select distinct id to create set with only IDs similar to:

data a;
   do id= 1 to 5;
   output;
   end;
run;
data b;
   do id = 3 to 7;
   output;
   end;
run;
data c;
   do id= 6 to 10;
   output;
   end;
run;

proc sql;
   create table Id as
   select Id from a
   union
   select Id from b
   union
   select Id from c;
quit;

[glcoolj12]

I like this route, but I have several thousand IDs (that are 8 digits long); moreover, ID is a character variable in my data.

[Patrick]

@glcoolj12

...but I have several thousand IDs (that are 8 digits long); moreover, ID is a character variable in my data

And why exactly would the solution @ballardw proposed not work for such a situation?

[ballardw]

Try the Proc SQL part pointing at your data sets and use the name of your ID variable. This will require the ID variable to have the same name in each set so may need some additional code in each select line if that isn't the case with your actual data. 

The data sets I created, and how, were just to show the syntax and that repeated values of ID did not end up in the results.

[glcoolj12]

When I run the code you suggested, I get an error stating "Ambiguous reference, column "ID" is in more than one table".

[ballardw]

Post the code with the error messages. Use a code box opened with the forum menu icon {i} above to preserve formatting.

Do you get the same error running the example code I posted with the data sets I created?

[LinusH]

Not sure why you would get a cateaian join from a full join. Perhaps if you could post some sample data?
If you have no duplicates why the DISTINCT?
To answer your question, yes you can have all joins in one SQL. And I don't see any need for subqueries that you use in your example. It you probably need to nest each full join result into the next join so you always join on the current full set of id columns. So in the end, the example by @ballardw with union is more feasable.

SELECT coalesce(x.id, c.id) as id, x.var1, x.var2, c.var3
From (
SELECT coalesce(a.id, b.id,) as id, a.var1, b.var2
From a
Full join b
On a.id =b.id) as x
Full join c
On x.id = c.id

And so on...

[glcoolj12]

Thank you everyone for your help! I restructured using the method @ballardw suggested and was successful. I'm very grateful for all your advice.