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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.