CODEWITHSUNDEEP
sas
Milad
Milad

Reputation: 339

Filtering a dataset by another dataset in SAS

I have the following two datasets. How can I exclude those observations in DATA1 that exist in DATA2

data DATA1;
   input Name $ 1-25;
   datalines;
Vincent, Martina   
Phillipon, Marie-Odile    
Gunter, Thomas            
Harbinger, Nicholas       
Benito, Gisela            
Rudelich, Herbert         
Sirignano, Emily          
Morrison, Michael         
;
run;

data DATA2;
   input Name $ 1-25;
   datalines;
Vincent, Martina   
Morrison, Michael         
;
run;

Upvotes: 1

Views: 3067

Answers (1)

Parfait
Parfait

Reputation: 107587

Classic example and often debated (which is most efficient) SQL query of the LEFT JOIN NULL vs. NOT IN vs NOT EXISTS.

Consider the proc sql equivalent solutions:

proc sql;
    create table Data3 as 
         select Data1.Name 
           from Data1 
      left join Data2 on Data1.Name = Data2.Name
          where Data2.Name is null;
quit;

proc sql;
    create table Data4 as 
         select Data1.Name 
           from Data1 
          where Data1.Name 
         not in (select Data2.Name from Data2);
quit;

proc sql;
    create table Data5 as 
         select Data1.Name 
           from Data1 
          where not exists 
         (select Data2.Name from Data2
          where Data1.Name = Data2.Name);
quit;

OUTPUT

Name 
Phillipon, Marie-Odile 
Gunter, Thomas 
Harbinger, Nicholas 
Benito, Gisela 
Rudelich, Herbert 
Sirignano, Emily

Upvotes: 2

Related Questions