topic Re: Non matching obsetvations in SAS Programming

Non matching obsetvations

PeterClemmensen — Thu, 22 Dec 2016 13:09:59 GMT

Hi All 🙂

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 🙂

Re: Non matching obsetvations

RW9 — Thu, 22 Dec 2016 13:19:16 GMT

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;

Re: Non matching obsetvations

Jagadishkatam — Thu, 22 Dec 2016 13:19:29 GMT

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;

Re: Non matching obsetvations

PeterClemmensen — Thu, 22 Dec 2016 13:24:16 GMT

Very cool, thank you both 🙂