BookmarkSubscribeRSS Feed
littlestone
Fluorite | Level 6
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.
9 REPLIES 9
CurtisMack
Fluorite | Level 6
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;
littlestone
Fluorite | Level 6
thank you for help. I guess join may not be the best choice for such operation. Maybe EXCEPT is better?
DBailey
Lapis Lazuli | Level 10
There's also a not exists.

select t1.*
from main t1
where not exists (select * from minor where key=t1.key);
quit;
Ksharp
Super User
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
littlestone
Fluorite | Level 6
thank you all for help.
DBailey
Lapis Lazuli | Level 10
proc sql;
select t1.*
from
main t1
left outer join minor t2
on t1.key=t2.key
where
t2.key is null;

quit;
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
chang_y_chung_hotmail_com
Obsidian | Level 7
@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

   */
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello Chang_y_chung,

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

Sincerely,
SPR

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 6465 views
  • 0 likes
  • 6 in conversation