how to get non matching observations

[rawindar]

hi all,

      I have two datasets as follows.Now i want to combine these boath datasets based on matching variable "no" and i want to get non matching observations from boath dataset in o/p .How can i get get it.Can anybody help me plz.

data a;

input no name$;

datalines;

1 x

2 y

3 z

;

data b;

input no sal;

datalines;

2 1000

4 2000

5 3000

;

i want o/p as follows

no name sal

1    x        .

3    z        .

4              2000

5              3000

This is an interview question

Thanks&Regards

Rawindar

[Patrick]

Untested code - but should be o.k:

data Match Nomatch;

     merge a (in=ina) b (in=inb);

     by no;

     if ina and inb then output match;

     else output Nomatch;

run;

[rawindar]

thanks patrick its working

[Ksharp · Accepted solution]

The same with Patrick's .

data a;
input no name$;
datalines;
1 x
2 y
3 z
;

data b;
input no sal;
datalines;
2 1000
4 2000
5 3000
;
data Match ;
     merge a (in=ina) b (in=inb);
     by no;
     if not ina or not inb ;
run;

Ksharp

[rawindar]

thnks ksharp

[Jagadishkatam]

Hi rawindar,

i used proc sql to produce the same O/P. the below code reduces few steps. Because in data step we need to sort the variable by proc sort before going for merge. In proc sql there is no need to sort.

proc sql;

select coalesce (a.no,b.no),a.name,b.sal from a full join b on a.no= b.no where a.no ne b.no;

quit;

[Ksharp]

I think it is vertical merge not horizontal merge. So an alternative SQL code could like

data a;
input no name$;
datalines;
1 x
2 y
3 z
;

data b;
input no sal;
datalines;
2 1000
4 2000
5 3000
;

proc sql;
create table want as
 select * from a where no ne all(select distinct no from b)
  outer union corresponding
 select * from b where no ne all(select distinct no from a);
quit;

Ksharp