DATA Step, Macro, Functions and more

Non matching obsetvations

Accepted Solution Solved
Reply
PROC Star
Posts: 551
Accepted Solution

Non matching obsetvations

Hi All Smiley Happy

 

I have the following problem. I have two datasets have1 and hav1 as below

 

data have1;
   input A $ B C;
   datalines;
   Peter 10 100
   Peter 20 300
   Anna  36 900
   Kelly 80 700
   Kelly 40 400
   Kelly 70 800
   Max   45 951
   Max   12 752
   ;

data have2;
   input A $ B C;
   datalines;
   Peter 10 100
   Anna  36 900
   Kelly 80 700
   Kelly 70 800
   Max   12 752
   James 46 943
   ;

Now I want to extract the observations in have1 that do not exist in have2, which in this case makes my WANT dataset looking like this

 

data want;
input A $ B C;
   datalines;
Peter 20 300
Kelly 40 400
Max   45 951
   ;

Thank you in advance Smiley Happy

 

 


Accepted Solutions
Solution
‎12-22-2016 08:23 AM
Super User
Super User
Posts: 7,401

Re: Non matching obsetvations

SQL has a nice except join for this:

proc sql;
  create table WANT as 
  select  A,B,C
  from    HAVE 1
  except select A,B,C from HAVE2;
quit;

View solution in original post


All Replies
Solution
‎12-22-2016 08:23 AM
Super User
Super User
Posts: 7,401

Re: Non matching obsetvations

SQL has a nice except join for this:

proc sql;
  create table WANT as 
  select  A,B,C
  from    HAVE 1
  except select A,B,C from HAVE2;
quit;
Trusted Advisor
Posts: 1,128

Re: Non matching obsetvations

consider that both the datasets have1 and hav1 are sorted by variables A and B.

data want;
merge have1(in=a) hav1(in=b);
by A B;
if a and not b;
run;
Thanks,
Jag
PROC Star
Posts: 551

Re: Non matching obsetvations

Very cool, thank you both Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 154 views
  • 1 like
  • 3 in conversation