Desktop productivity for business analysts and programmers

FCMP Function with HASH joins

Reply
Occasional Contributor
Posts: 7

FCMP Function with HASH joins

[ Edited ]

Hello all,

 

So I am trying to build lookup functions within a medicare database to easily get things like birthdate, deathdate, race and so on. The error I get when I run it is:

 

Unable to define key/date 'BENE_RACE_CD' 'BENE_BIRTH_DT' 'for HASH object 'lookup'. Not found in dataset.

 

and 

 

Error reported in funtion 'DEFINEDATA_HASH_' in statement number 4 at line 9 column 4.

 

these errors are especially annoying because the first time I ran it they worked. Then they did stopped working.

 

FCMP.PNG

 

 

The keep is in the dataset portion because if I don't use it I run out of memory (and the server I use is not very flexible when it comes to setting memory).

 

I have attached an image of my code since the server I am on does not let me copy things easily off of it.

 

Thanks for any help

 

 

 

 edit: I put the wrong variable in the error, but I posted the actual error below.

Super User
Posts: 3,768

Re: FCMP Function with HASH joins

The variable you refer to BENE_RACE_CD doesn't appear in the code you posted. It would be helpful not only to post the correct FCMP function code but also the DATA step log in which you are using it including error messages.

 

How big is your lookup tables? Remember hash tables have to be loaded into memory so unless you can guarantee you are always going to have enough memory, then using HASH table techniques could be problematic.

Occasional Contributor
Posts: 7

Re: FCMP Function with HASH joins

[ Edited ]

datastepLog.PNGHere is the log from the datastep. Initially the lookup was a 14million x 8 table. For this I was testing with a 500 x 2 table.

Super User
Posts: 3,768

Re: FCMP Function with HASH joins

Wouldn't BENE_BIRTH_DT need to be in your KEEP = variables? Also 14M rows is getting pretty big for a hash table, depending on how much memory you have available. What is your SAS MEMSIZE option setting?

Occasional Contributor
Posts: 7

Re: FCMP Function with HASH joins

Yes, you are correct.

 

There are 4 nearly identical functions that I wrote and I keep mixing them up when posting. Basically the keep has two variables, the first is the key which is bene_id, the second is one of the following: bene_death_dt, bene_birth_dt, bene_race_cd, or bene_sex_cd. I double-checked the code to make sure everything is consistent in each function and it is.  The error is consistent regardless of which function I use (telling me the second variable I keep is not present).

 

What is also annoying is if I create a new set that does the keep first that is accessed by the function without a keep then it works (at least with the test set of 500).

 

What is becoming quite apparent is that this seems like a case of me using the wrong tool for the job. Hash is probably not the answer, but it still is bugging me that the keep does not seem to be working.

 

 

 

Super User
Posts: 3,768

Re: FCMP Function with HASH joins

Hash works best with highly predictable data and static keys. I agree you are probably using the wrong tool here.

Respected Advisor
Posts: 4,548

Re: FCMP Function with HASH joins

@sturg1dj

You'd probably better off implementing this via a SAS Macro where you pass in source variables (what you use in your hash table) and target variable as parameters.

Respected Advisor
Posts: 4,548

Re: FCMP Function with HASH joins

@sturg1dj

Besides of everything else I believe your code would recreate and reload a hash table for every single call of the function. That's certainly something you don't want to do.

Ask a Question
Discussion stats
  • 7 replies
  • 185 views
  • 5 likes
  • 3 in conversation