BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_inquisitive
Lapis Lazuli | Level 10

I want to peform left outer join in hash with following code (so as to reproduce the result of left join in Proc Sql).  

 

data a;
input key d1 d2 d3;
datalines;
1 1 1 1
1 2 2 2
1 3 3 3
2 1 1 1
2 2 2 2
4 1 1 1
4 1 1 1
5 5 5 5
;

data b;
input key d4 d5 d6 other;
datalines;
1 1 1 1 0
2 2 2 2 0
3 3 3 3 0
4 4 4 4 0
;
data test(drop=rc);
if 0 then set A;
if _n_=1 then do;
dcl hash h(dataset:'A',multidata:'yes');
h.definekey('key');
h.definedata('d1','d2','d3');
h.definedone();
end;
set B (keep=key d4 d5 d6);
rc=h.find();
do while(rc=0);
output;
rc=h.find_next();
end;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
mohamed_zaki
Barite | Level 11

Based on that A is your lookup table 


data new_test1(drop=rc r);
if 0 then set a;
declare hash hh_pat(dataset:"a", multidata: "y");
rc=hh_pat.defineKey("key");
rc=hh_pat.defineData("d1", "d2", "d3");
rc=hh_pat.defineDone();
 
do until(eof);
 	set b end=eof;
 	call missing(d1, d2, d3); 
 	rc=hh_pat.find();
 	output;
	if (rc = 0) then do;
		hh_pat.has_next(result: r);
			do while(r ne 0);
				hh_pat.find_next();
 				output;
				hh_pat.has_next(result: r);
			end;
	end;
end;
stop;
run;

That should be identical to

proc sql ;
select b.key, a.d1, a.d2, a.d3 , b.d4, b.d5 , b.d6
from b left join a
on b.key=a.key;
quit;

 

View solution in original post

6 REPLIES 6
mohamed_zaki
Barite | Level 11

Based on that A is your lookup table 


data new_test1(drop=rc r);
if 0 then set a;
declare hash hh_pat(dataset:"a", multidata: "y");
rc=hh_pat.defineKey("key");
rc=hh_pat.defineData("d1", "d2", "d3");
rc=hh_pat.defineDone();
 
do until(eof);
 	set b end=eof;
 	call missing(d1, d2, d3); 
 	rc=hh_pat.find();
 	output;
	if (rc = 0) then do;
		hh_pat.has_next(result: r);
			do while(r ne 0);
				hh_pat.find_next();
 				output;
				hh_pat.has_next(result: r);
			end;
	end;
end;
stop;
run;

That should be identical to

proc sql ;
select b.key, a.d1, a.d2, a.d3 , b.d4, b.d5 , b.d6
from b left join a
on b.key=a.key;
quit;

 

SAS_inquisitive
Lapis Lazuli | Level 10

@mohamed_zaki.  Thanks.  I also want this way too.

 

proc sql ;
select b.key, a.d1, a.d2, a.d3 , b.d4, b.d5 , b.d6
from a left join b
on a.key=b.key;
quit;

 

 

mohamed_zaki
Barite | Level 11

As your lookup table B is not having duplicate, then this is easier

data test(drop=rc);
 if 0 then set b;
 declare hash hh_pat(dataset:"b");
 rc=hh_pat.defineKey("key");
 rc=hh_pat.defineData("d4", "d5", "d6","other");
 rc=hh_pat.defineDone();
 do until(eof);
 set a end=eof;
 call missing(d4, d5, d6, other);
 rc=hh_pat.find();
 output;
 end;
 stop;
run;

or you can modify the previous post code if you will have duplicate.

SAS_inquisitive
Lapis Lazuli | Level 10

@ mohamed_zaki, I guess we can't do this while keeping data set  "a" in hash table, right?

mohamed_zaki
Barite | Level 11

Yes, as A contains duplicate records based on your key. So you will end up with uncomplete left joined data set. Only if you do specify the option multidata="Y". And by that the hash object keeps the multiple values in a list that is associated with the key. Which can be traversed and manipulated by using several methods such as HAS_NEXT or FIND_NEXT as in the first post.

 

Ksharp
Super User

Your code looks good. Except:

 

data a;
input key d1 d2 d3;
datalines;
1 1 1 1
1 2 2 2
1 3 3 3
2 1 1 1
2 2 2 2
4 1 1 1
4 1 1 1
5 5 5 5
;
data b;
input key d4 d5 d6 other;
datalines;
1 1 1 1 0
2 2 2 2 0
3 3 3 3 0
4 4 4 4 0
;
data test(drop=rc);
if _n_=1 then do;
if 0 then set A;
dcl hash h(dataset:'A',multidata:'yes');
h.definekey('key');
h.definedata('d1','d2','d3');
h.definedone();
end;
call missing(of _all_);
set B (keep=key d4 d5 d6);
rc=h.find();
if rc ne 0 then output;
do while(rc=0);
output;
rc=h.find_next();
end;
run;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2295 views
  • 2 likes
  • 3 in conversation