topic Re: SAS Hash Object for duplicating rows in SAS Programming

SAS Hash Object for duplicating rows

_SAS_ — Fri, 02 Sep 2016 17:20:43 GMT

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.

Re: SAS Hash Object for duplicating rows

Ksharp — Sat, 03 Sep 2016 03:00:22 GMT

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;

Re: SAS Hash Object for duplicating rows

_SAS_ — Sat, 03 Sep 2016 05:14:39 GMT

Thanks a lot for your help 🙂