topic data merging in SAS Data Management

data merging

sijansap — Fri, 14 Dec 2018 03:15:28 GMT

Got a data set merging problem:

Data Have1;

a1	a2	a3
.	4	3
1	.	.
2	3	4
4	9	3
9	5	2

data have2;

a1	b1	b2
1	6	5
2	4	.
9	2	3
12	1	1

I want to merge data Have1 and Have2 in such a way that if the values of the variable a1 in Have1 match with values of a1 in Have2 then keep otherwise drop. For example I want the new set like:

a1	a2	a3	b1	b2
1	.	.	6	5
2	3	4	4	.
9	5	2	2	3

I would appreciate very much if any expert out there help me out to get the result.

Thank you very much.

Sijansap

Re: data merging

r_behata — Fri, 14 Dec 2018 03:18:13 GMT

data want;
merge have1(in=a) have2(in=b);
by a1;
if a and b;
run

Re: data merging

singhsahab — Fri, 14 Dec 2018 05:49:04 GMT

Hello,

here is an alternative method :

data want;
if 0 then set have2;
if _n_=1 then do;
declare hash h1(dataset:'have2');
h1.definekey('a1');
h1.definedata(all:'y');
h1.definedone();
end;
set have1;
 r1=h1.find(key:a1);
 if r1=0;
 drop r1;
run;

Thanks 🙂

Re: data merging

Kurt_Bremser — Fri, 14 Dec 2018 07:48:23 GMT

And the SQL method:

proc sql;
create table want as
select
  a.a1,
  a.a2,
  a.a3,
  b.b1,
  b.b2
from have1 a, have2 b
where a.a1 = b.a1;
quit;

Re: data merging

sijansap — Fri, 14 Dec 2018 14:13:35 GMT

Thank you r_behata.
Your codes were short and simple and worked just the way I wanted.

Re: data merging

sijansap — Fri, 14 Dec 2018 14:41:54 GMT

This works well when there are repeated values of a1 in Have1. Thank you KurtBremser.