topic Re: how to get non matching observations in SAS Procedures

how to get non matching observations

rawindar — Sat, 28 Apr 2012 10:13:47 GMT

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

Re: how to get non matching observations

Patrick — Sat, 28 Apr 2012 10:50:15 GMT

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;

Re: how to get non matching observations

Ksharp — Sat, 28 Apr 2012 11:50:12 GMT

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

Re: how to get non matching observations

rawindar — Sat, 28 Apr 2012 14:31:59 GMT

thanks patrick its working

Re: how to get non matching observations

rawindar — Sat, 28 Apr 2012 14:32:36 GMT

thnks ksharp

Re: how to get non matching observations

Jagadishkatam — Sun, 29 Apr 2012 04:32:50 GMT

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;

Re: how to get non matching observations

Ksharp — Sun, 29 Apr 2012 08:20:10 GMT

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