Hi,
Please consider the example in the code below.
data one;
length
key $4
name $10
address $20
;
input key $ name $ address $;
datalines;
a John Street_123
b Mark Drive_456
c Martin Boulevard_789
run;
data two;
length
key $4
phone $10
email $20
;
input key $ phone $ email $;
datalines;
a 1234 abc@abc
b 5678 def@def
c 91011 ghi@ghi
a 121314 jkl@jkl
run;
data three;
length
phone $10
email $20
;
set one;
if _N_=1 then do;
declare hash h(dataset:"work.two");
h.definekey("key");
h.definedata("phone", "email");
h.definedone();
end;
if h.find() eq 0 then do;
put phone email;
end;
run;
proc sql;
create table four as
select t1.key, t1.name, t1.address,
t2.phone, t2.email
from one t1 inner join two t2 on t1.key = t2.key;
run;
My question - is there a chance to modify the hash object call to generate the same kind of result as for the querry four?
What i mean is that the hash table seems to take the first observation where the key matches (just like a vlookup in xls) but it does not perform a cartesian product like the inner join in the proc sql.
Is it possible to change this behavior?
Thank you.
Of course, Hash Table can do that . data one; length key $4 name $10 address $20 ; input key $ name $ address $; datalines; a John Street_123 b Mark Drive_456 c Martin Boulevard_789 ; run; data two; length key $4 phone $10 email $20 ; input key $ phone $ email $; datalines; a 1234 abc@abc b 5678 def@def c 91011 ghi@ghi a 121314 jkl@jkl ; run; data three; if _N_=1 then do; if 0 then set work.two(rename=(phone=p2 email=e2)); declare hash h(dataset:"work.two(rename=(phone=p2 email=e2))",multidata:'y'); h.definekey("key"); h.definedata("p2", "e2"); h.definedone(); end; set one; rc=h.find(); do while(rc=0); output; rc=h.find_next(); end; drop rc; run;
Of course, Hash Table can do that . data one; length key $4 name $10 address $20 ; input key $ name $ address $; datalines; a John Street_123 b Mark Drive_456 c Martin Boulevard_789 ; run; data two; length key $4 phone $10 email $20 ; input key $ phone $ email $; datalines; a 1234 abc@abc b 5678 def@def c 91011 ghi@ghi a 121314 jkl@jkl ; run; data three; if _N_=1 then do; if 0 then set work.two(rename=(phone=p2 email=e2)); declare hash h(dataset:"work.two(rename=(phone=p2 email=e2))",multidata:'y'); h.definekey("key"); h.definedata("p2", "e2"); h.definedone(); end; set one; rc=h.find(); do while(rc=0); output; rc=h.find_next(); end; drop rc; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.