Solved
Contributor
Posts: 70

# how to get non matching observations

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

Accepted Solutions
Solution
‎04-28-2012 07:50 AM
Super User
Posts: 10,787

## Re: how to get non matching observations

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

All Replies
Respected Advisor
Posts: 4,736

## Re: how to get non matching observations

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;

Contributor
Posts: 70

## Re: how to get non matching observations

thanks patrick its working

Solution
‎04-28-2012 07:50 AM
Super User
Posts: 10,787

## Re: how to get non matching observations

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

Contributor
Posts: 70

thnks ksharp

Trusted Advisor
Posts: 1,147

## Re: how to get non matching observations

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;

Thanks,
Jag
Super User
Posts: 10,787

## Re: how to get non matching observations

Posted in reply to Jagadishkatam

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

🔒 This topic is solved and locked.

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

Discussion stats
• 6 replies
• 13148 views
• 3 likes
• 4 in conversation