Help using Base SAS procedures

how to get non matching observations

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

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,041

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

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,041

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

Re: how to get non matching observations

thnks ksharp

Trusted Advisor
Posts: 1,137

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,041

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
  • 8286 views
  • 2 likes
  • 4 in conversation