topic Re: FCMP Function with HASH joins in SAS Enterprise Guide

FCMP Function with HASH joins

sturg1dj — Sat, 14 Apr 2018 16:25:20 GMT

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.

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.

Re: FCMP Function with HASH joins

SASKiwi — Sat, 14 Apr 2018 02:18:23 GMT

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.

Re: FCMP Function with HASH joins

sturg1dj — Sat, 14 Apr 2018 16:26:01 GMT

Here 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.

Re: FCMP Function with HASH joins

SASKiwi — Sat, 14 Apr 2018 23:07:16 GMT

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?

Re: FCMP Function with HASH joins

Patrick — Sun, 15 Apr 2018 05:46:31 GMT

@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.

Re: FCMP Function with HASH joins

sturg1dj — Sun, 15 Apr 2018 19:15:23 GMT

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.

Re: FCMP Function with HASH joins

SASKiwi — Sun, 15 Apr 2018 20:47:44 GMT

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

Re: FCMP Function with HASH joins

Patrick — Sun, 15 Apr 2018 21:35:50 GMT

@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.