topic FULL JOIN with Multiple Datasets in ID variable (one record per ID) in SAS Data Management

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

glcoolj12 — Tue, 01 Aug 2017 19:07:02 GMT

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

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

ballardw — Tue, 01 Aug 2017 19:46:35 GMT

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;

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

glcoolj12 — Tue, 01 Aug 2017 20:11:36 GMT

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

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

Patrick — Tue, 01 Aug 2017 20:31:50 GMT

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

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

ballardw — Tue, 01 Aug 2017 22:13:07 GMT

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.

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

LinusH — Wed, 02 Aug 2017 05:27:05 GMT

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

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

glcoolj12 — Wed, 02 Aug 2017 18:26:52 GMT

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

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

ballardw — Wed, 02 Aug 2017 19:30:29 GMT

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?

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

glcoolj12 — Thu, 03 Aug 2017 17:10:41 GMT

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