SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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

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...

Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 10,538

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

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;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 10,538

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

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;
Occasional Contributor
Posts: 16

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

[ Edited ]

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

Respected Advisor
Posts: 3,901

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

@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?

Super User
Posts: 10,538

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

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.

Occasional Contributor
Posts: 16

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

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

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

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?

Super User
Posts: 5,260

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

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...
Data never sleeps
Occasional Contributor
Posts: 16

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

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

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

Discussion stats
  • 8 replies
  • 529 views
  • 1 like
  • 4 in conversation