Combine two datasets

[Saba1]

Hi

I am struggling to find a code that can help me combining two datasets i.e. one and two.

data one;
 input X Y $ Z;
 datalines;
1 a 0.1
1 b 0.2
1 c 0.5
2 c 0.8
2 d 0.5
2 e 0.7
;


data two;
 input Y $ R;
 datalines;
a 5
b 6
c 9
d 7
e 2
f 4
;

data Want;
 input X Y $ Z R;
 datalines;
1 a 0.1 5
1 b 0.2 6
1 c 0.5 9
1 d . 7
1 e . 2
1 f . 4
2 a . 5
2 b . 6
2 c 0.8 9
2 d 0.5 7
2 e 0.7 2
2 f . 4
;

run;

Kindly guide me in this regard.

Thanks

[novinosrin]

data one;
 input X Y $ Z;
 datalines;
1 a 0.1
1 b 0.2
1 c 0.5
2 c 0.8
2 d 0.5
2 e 0.7
;


data two;
 input Y $ R;
 datalines;
a 5
b 6
c 9
d 7
e 2
f 4
;

data want ;
 if 0 then set one two;
   dcl hash H (dataset: "two") ;
   h.definekey  ("y") ;
   h.definedata ("y","r") ;
   h.definedone () ;
do until(last.x);
 set one;
 by x;
 if h.find() ne 0 then call missing(r);
 else h.remove();
 output;
end;
call missing(r,z);
dcl hiter hi('h');
do while(hi.next()=0);
 output;
end;
h.delete();
run;

[Astounding]

If you would like a simpler program, it can be (probably) done but requires an extra step:

proc sql;
   create table XLIST as select distinct X from one;
   create table shell as select * from two, xlist order by x, y;
quit;

data want;
   merge shell one;
   by X Y;
run;

When I say "probably", note two things:

• This code is untested and my SQL syntax is rusty.
• The results rely on the data set ONE already being in sorted order.

[Ksharp · Accepted solution]

data one;
 input X Y $ Z;
 datalines;
1 a 0.1
1 b 0.2
1 c 0.5
2 c 0.8
2 d 0.5
2 e 0.7
;


data two;
 input Y $ R;
 datalines;
a 5
b 6
c 9
d 7
e 2
f 4
;

proc sql;
create table want as
select a.*,z from
(select * from (select distinct x from one),two) as a
left join one as b on a.x=b.x and a.y=b.y
;
quit;