Is it possible to make a full joins with the use of hash object? the result to be obtained is the following (with proc sql):
data tab1;
input var1 value;
datalines;
1 11
1 11
2 22
3 33
9 88
run;
data tab2;
input var1 value;
datalines;
1 11
2 22
3 99
4 999
run;
PROC SQL;
create table tab_fullj as
select A.var1 as A_var1 ,
B.var1 as B_var1 ,
A.value as A_value,
B.value as B_value ,
COALESCE (A.var1,b.var1) as coal_var1,
COALESCE (A.value,b.value) as coal_value
from
tab2 as A
full join
tab1 as B
on a.var1=b.var1;
quit;
Sure.That is more complicated than you can image.
data tab1;
input a_var1 a_value;
datalines;
1 11
1 11
2 22
3 33
9 88
run;
data tab2;
input b_var1 b_value;
datalines;
1 11
2 22
3 99
4 999
run;
data want;
if _n_ eq 1 then do;
if 0 then set tab2;
declare hash h(dataset:'tab2',multidata:'y');
declare hiter hi('h');
h.definekey('b_var1');
h.definedata('b_var1','b_value');
h.definedone();
end;
set tab1 end=last;
by a_var1;
if first.a_var1 then call missing(b_var1,b_value);
if h.find(key:a_var1)=0 then h.removedup(key:a_var1);
coal_var1=COALESCE(b_var1,A_var1);
coal_value=COALESCE(b_value,A_value);
output;
if last.a_var1 then do;
rc=h.find(key:a_var1);
do while(rc=0);
coal_var1=COALESCE(b_var1,A_var1);
coal_value=COALESCE(b_value,A_value);
output;
rc=h.find_next(key:a_var1);
end;
end;
if last then do;
call missing(of _all_);
do while(hi.next()=0);
coal_var1=COALESCE(b_var1,A_var1);
coal_value=COALESCE(b_value,A_value);
output;
end;
end;
drop rc;
run;
proc print noobs;run;
Sure.That is more complicated than you can image.
data tab1;
input a_var1 a_value;
datalines;
1 11
1 11
2 22
3 33
9 88
run;
data tab2;
input b_var1 b_value;
datalines;
1 11
2 22
3 99
4 999
run;
data want;
if _n_ eq 1 then do;
if 0 then set tab2;
declare hash h(dataset:'tab2',multidata:'y');
declare hiter hi('h');
h.definekey('b_var1');
h.definedata('b_var1','b_value');
h.definedone();
end;
set tab1 end=last;
by a_var1;
if first.a_var1 then call missing(b_var1,b_value);
if h.find(key:a_var1)=0 then h.removedup(key:a_var1);
coal_var1=COALESCE(b_var1,A_var1);
coal_value=COALESCE(b_value,A_value);
output;
if last.a_var1 then do;
rc=h.find(key:a_var1);
do while(rc=0);
coal_var1=COALESCE(b_var1,A_var1);
coal_value=COALESCE(b_value,A_value);
output;
rc=h.find_next(key:a_var1);
end;
end;
if last then do;
call missing(of _all_);
do while(hi.next()=0);
coal_var1=COALESCE(b_var1,A_var1);
coal_value=COALESCE(b_value,A_value);
output;
end;
end;
drop rc;
run;
proc print noobs;run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.