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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.