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
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;
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;
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:
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: