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
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
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;
thanks patrick its working
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
thnks ksharp
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;
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.