DATA Step, Macro, Functions and more

full join with hash object

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

full join with hash object

[ Edited ]

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;

 


Accepted Solutions
Solution
‎03-13-2016 09:22 AM
Super User
Posts: 9,676

Re: full join with hash object

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;

View solution in original post


All Replies
Super User
Posts: 5,256

Re: full join with hash object

I guess, buy why?
Data never sleeps
Solution
‎03-13-2016 09:22 AM
Super User
Posts: 9,676

Re: full join with hash object

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 272 views
  • 0 likes
  • 3 in conversation