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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.