BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
_SAS_
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

2 REPLIES 2
Ksharp
Super User
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;



_SAS_
Obsidian | Level 7
Thanks a lot for your help 🙂

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
  • 2 replies
  • 859 views
  • 0 likes
  • 2 in conversation