Lookup table value using Hash

Reply
Contributor
Posts: 24

Lookup table value using Hash

[ Edited ]

Hi,

 

I have a small table :

key            value

 

M&M          Chocolate

Sneakers   Chocolate

Almonds     Nuts

kellog's       Cereals

 

And i have a large table with field in which the key value has to be looked up for. However there isn't a exact match.It has to be looked up with an index statement.

 

Example :

I need to look M&M in "5028231_GoPro WalmartRetail-NewYork-M&M"

 

I am using the following code.

 


data Need;

	set Ref_table ;
	length campaign_key $500 business $200; 
	if _n_=1 then do;
		DECLARE hash Vertical(dataset: "Ref_table",multidata:'y') ;
	 	Vertical.defineKey('key');
	 	Vertical.defineData('key','value');
	 	Vertical.defineDone(); 
	end;
	
	do until(eof);
		set Have end= eof;
		if index(Campaign,value)>0 then do;
			output Need;
			
		end;
	
   	end;

run;

The issue with the code is that it works only with the first row of the reference table. 

I know it can be completely wrong too. Could you please help me with this.

 

Thanks

 

Trusted Advisor
Posts: 1,553

Re: Lookup table value using Hash

Posted in reply to SASEnthusiast

I have no much experience with hashing, but according to an example I have, try the next code:

 

data Need;

	DECLARE hash Vertical(dataset: "Ref_table",multidata:'y') ;
 	  rc = Vertical.defineKey('key');
 	  rc = Vertical.defineData('key','value');
 	  rc = Vertical.defineDone();

length campaign_key $500 business $200; 
do until (eof1);
set Ref_table end=eof1;
rc = vertical.add();
end; do until(eof2); set Have end= eof2; if index(Campaign,value)>0 then do; output Need; end; end; run;

In case it did not fulfill your expectations, look for documentation on HITER HASH. 

Trusted Advisor
Posts: 1,553

Re: Lookup table value using Hash

or look for:   

Using the Hash Iterator Object
Super User
Posts: 10,018

Re: Lookup table value using Hash

Posted in reply to SASEnthusiast
CODE NOT TEST:



data Need;

	set Ref_table ;
	length campaign_key $500 business $200; 
	if _n_=1 then do;
		DECLARE hash Vertical(dataset: "Ref_table",multidata:'y') ;
	 	Vertical.defineKey('key');
	 	Vertical.defineData('key','value');
	 	Vertical.defineDone(); 
	end;
	
	  
		set Have ;
             rc= Vertical.find();
           do while(rc=0);
		if find(Campaign,strip(value)) then output ;			
	       rc= Vertical.find_next();
	end;
   	

run;


Ask a Question
Discussion stats
  • 3 replies
  • 249 views
  • 0 likes
  • 3 in conversation