DATA Step, Macro, Functions and more

SAS Hash Object for duplicating rows

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

SAS Hash Object for duplicating rows

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.


Accepted Solutions
Solution
‎09-03-2016 01:14 AM
Super User
Posts: 9,681

Re: SAS Hash Object for duplicating rows

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;



View solution in original post


All Replies
Solution
‎09-03-2016 01:14 AM
Super User
Posts: 9,681

Re: SAS Hash Object for duplicating rows

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;



Contributor
Posts: 24

Re: SAS Hash Object for duplicating rows

Thanks a lot for your help Smiley Happy
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 202 views
  • 0 likes
  • 2 in conversation