topic full join with hash object in SAS Programming

full join with hash object

mariopellegrini — Sat, 12 Mar 2016 10:15:29 GMT

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;

Re: full join with hash object

LinusH — Sat, 12 Mar 2016 13:43:57 GMT

I guess, buy why?

Re: full join with hash object

Ksharp — Sun, 13 Mar 2016 03:44:42 GMT

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&colon;'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;