topic Re: proc sql: opposite to inner join in SAS Procedures

proc sql: opposite to inner join

littlestone — Fri, 11 Mar 2011 21:34:27 GMT

Hi, all

Suppose I have two data sets Main and Minor each of which has a Variable called Key.

Main Key
1
2
3
4
5

Minor Key
1
3
5
7
9

Inner Join on Key will give:
1
3
5

However what i want are those Main Key that are NOT included in Minor Key, i.e., the result I am looking for is:

2
4

What kind of join can do this? or How to write Proc SQL codes to do this? Thanks.

Re: proc sql: opposite to inner join

CurtisMack — Fri, 11 Mar 2011 21:43:28 GMT

This is how I would do it, but there may be a better way:

data set1;
x = 1 ; output;
x = 2 ; output;
x = 3 ; output;
x = 4 ; output;
x = 5 ; output;
run;
data set2;
x = 1 ; output;
x = 3 ; output;
x = 5 ; output;
x = 7 ; output;
x = 9 ; output;
run;
proc sql;
create table NotInner as
select set1.x
from set1
left join
set2
on set1.x = set2.x
where set2.x is null;
quit;

Re: proc sql: opposite to inner join

littlestone — Sat, 12 Mar 2011 04:23:06 GMT

thank you for help. I guess join may not be the best choice for such operation. Maybe EXCEPT is better?

Re: proc sql: opposite to inner join

DBailey — Sat, 12 Mar 2011 23:14:29 GMT

proc sql;
select t1.*
from
main t1
left outer join minor t2
on t1.key=t2.key
where
t2.key is null;

quit;

Re: proc sql: opposite to inner join

DBailey — Sat, 12 Mar 2011 23:18:14 GMT

There's also a not exists.

select t1.*
from main t1
where not exists (select * from minor where key=t1.key);
quit;

Re: proc sql: opposite to inner join

Ksharp — Mon, 14 Mar 2011 03:02:54 GMT

Hi.

data set1;

x = 1 ; output;

x = 2 ; output;

x = 3 ; output;

x = 4 ; output;

x = 5 ; output;

run;

data set2;

x = 1 ; output;

x = 3 ; output;

x = 5 ; output;

x = 7 ; output;

x = 9 ; output;

run;

proc sql;

/* this way is suited only for all variables has the same value*/

create table t1 as

select *

from set1

except

select *

from set2;

/*another way*/

create table t2 as

select *

from set1

where x not in (select x from set2);

quit;

Ksharp Message was edited by: Ksharp

Re: proc sql: opposite to inner join

littlestone — Tue, 15 Mar 2011 20:09:10 GMT

thank you all for help.

Re: proc sql: opposite to inner join

SPR — Wed, 16 Mar 2011 13:50:08 GMT

Hello Littlestone,

You asking for proc SQL but the best way to do it is to use a datastep merge like this:
[pre]
data Inner Notinner;
merge set1(in=s1) set2 (in=s2);
if s1 and s2 then output Inner;
if s1 and not s2 then output Notinner;
by x;
run;
[/pre]
This merge requires a preliminary sorting of set1 and set2 but simultaneously creates two desired outputs.
Sincerely,
SPR

Re: proc sql: opposite to inner join

chang_y_chung_hotmail_com — Wed, 16 Mar 2011 14:48:26 GMT

@SPR: In the case that the input datasets have some common satellite (or non-key) vars, your output dataset can be incorrect. Here's an example:

   data one;

     input id var @@;

   cards;

   1 1 2 2 3 3 4 4 5 5

   ;

   run;

   data two;

      input id var @@;

   cards;

   1 6 3 7 5 8 7 9 9 10

   ;

   run;

   proc sort data=one; by id; run;

   proc sort data=two; by id; run;

   data inner notinner;

     merge one(in=s1) two(in=s2);

     if s1 and s2 then output Inner;

     if s1 and not s2 then output Notinner;

     by id;

   run;

   proc print data=inner;

   run;

   /* on lst -- Uh-oh!

   Obs    id    var

    1      1     6

    2      3     7

    3      5     8

   */

Re: proc sql: opposite to inner join

SPR — Wed, 16 Mar 2011 19:05:07 GMT

Hello Chang_y_chung,

It is very easy to overcome this obstacle simply renaming VAR for the second dataset.

Sincerely,
SPR