Data step/SQL Join/Merge/Union 2 datasets/tables and remove the same rows/observations

Question

For example, I have 2 tables like this

data have;
input name $ status $;
datalines;
A    a
B    b
C    c
;;;;
run;

2nd table:

data addon;
input name $ status $;
datalines;
A    a
C    f
D    d
E    e
F    f
B    z
;;;;
run;

How do I get the result like below:

B    b
C    c
C    f
D    d
E    e
F    f
B    z

The row A - a is the same from 2 tables so it got removed. I'm trying to use left join but the result is not right. Please help and thanks in advance. I'm really appreciated it.

I have a solution for you, but I am getting an error message trying to submit. Here is a link to a pastebin of it: http://pastebin.com/ZGnLECuH — Ignacio, Apr 01 '13 at 04:14
Why are you tagging this mysql, if it's SAS? They're not the same language. Are you actually running this in SAS, or in MySQL? — Joe, Apr 01 '13 at 12:29
Sorry it's my mistake, it should be SQL, because in SAS it also accepts sql procedure. — Luke Henz, Apr 02 '13 at 01:14

score 1 · Answer 1 · answered Apr 01 '13 at 05:42

Try this:

  SELECT COALESCE(table1.input, table2.input) AS input
         , COALESCE(table1.status, table2.status) AS status
    FROM table1
         FULL OUTER JOIN table2 ON table1.input = table2.input
         AND table1.status = table2.status
   WHERE (table1.input IS NULL OR table2.input IS NULL)
ORDER BY 1

Output :

INPUT STATUS
----- ------
B     b      
B     z      
C     f      
C     c      
D     d      
E     e      
F     f

score 1 · Answer 2 · answered Apr 01 '13 at 12:29

Don't have time to test this, but this is approximately right. Won't work in SQLFiddle since MySQL doesn't support except.

select * from (
  select * from have union select * from addon)
except
( select * from have, addon 
   where have.status=addon.status and have.name=addon.name)

score 1 · Accepted Answer · answered Apr 01 '13 at 13:37

Another way

data have;
 input name $ status $;
 datalines;
A    a
B    b
C    c
;;;;
run;

data addon;
 input name $ status $;
datalines;
A    a
C    f
D    d
E    e
F    f
B    z
;;;;
run;

Data Together;
 Set have addon;
 /* If the data sets were already sorted */
 /* By Name Status;                      */
/*  Then skip the Proc Sort              */
Run;

Proc sort data=together;
 by name status;
Run;

Data final;
 Set Together;
 by name status;
 if first.status and last.status;
Run;

Thanks for your help. I accepted this answer since it has the best performance of all. Thanks again. — Luke Henz, Apr 02 '13 at 01:15

hodl · Answer 4 · 2013-04-01T04:48:11.230

0

SELECT t1.name,t1.status
FROM 
  (
    SELECT name,status
    FROM have
    UNION ALL
    SELECT name,status
    FROM addon
  ) as t1
JOIN have t2 ON t1.name!=t2.name AND t1.status!=t2.status
JOIN addon t3 ON t2.name=t3.name AND t2.status=t3.status

I created an SQL fiddle for you.

edited Apr 01 '13 at 04:48

answered Apr 01 '13 at 04:27

hodl

1,420
12
21

score 0 · Answer 5 · answered Apr 02 '13 at 11:25

data have;
input name $ status $;
datalines;
A    a
B    b
C    c
;;;;
run;

2nd table:

data addon;
input name $ status $;
datalines;
A    a
C    f
D    d
E    e
F    f
B    z
;;;;
run;

How do I get the result like below:

B    b
C    c
C    f
D    d
E    e
F    f
B    z

Simple Use merge statement. Sort the datasets using the keys before this step

DATA RESULT;
KEEP H.NAME A.STATUS;
MERGE HAVE(IN = H) ADDON (IN = A);
BY NAME;
RUN;

Data step/SQL Join/Merge/Union 2 datasets/tables and remove the same rows/observations

5 Answers5