SAS Hash Tables: Is there a way to find/join on different keys or have optional keys

user_14280582 0
Viewed 708

I frequently work with some data for which the keys are not perfect, and I need to join data from a difference source, I want to continue using Hash Objects for the speed advantage however when I am using a lot of data I can run into crashes (memory restraints).

A simplified overview is I have 2 different keys which are all unique but not present for every record, we will call them Key1 and Key2.

My current solution, which is not very elegant (but it works) is to do the following:

if _N_ = 1 then do;
   declare hash h1(Dataset:"DataSet1");
                h1.DefineKey("key1");
                h1.DefineData("Value");
                h1.DefineDone();
   declare hash h2(Dataset:"DataSet1");
                h2.DefineKey("key2");
                h2.DefineData("Value");
                h2.DefineDone();
end;

set DataSet2;

rc = h1.find();
if rc NE 0 then do;
    rc = h2.find();
end;

So I have exactly the same dataset in two hash tables, but with 2 different keys defined, if the first key is not found, then I try to find the second key.

Does anyone know of a way to make this more efficient/easier to read/less memory intensive?

Apologies if this seems a bad way to accomplish the task, I absolutely welcome criticism so I can learn!

Thanks in advance,

Adam.

sas hashtable
2 Answers

I am a huge proponent of hash table lookups - they've helped me do some massive multi hundred-million row joins in minutes that otherwise would could have taken hours.

The way you're doing it isn't a bad route. If you find yourself running low on memory, the first thing to identify is how much memory your hash table is actually using. This article by sasnrd shows exactly how to do this.

Once you've figured out how much it's using and have a benchmark, or if it doesn't even run at all because it runs out of memory, you can play around with some options to see how they improve your memory usage and performance.

1. Include only the keys and data you need

When loading your hash table, exclude any unnecessary variables. You can do this before loading the hash table, or during. You can use dataset options to help reduce table size, such as where, keep, and drop.

dcl hash h1(dataset: 'mydata(keep=key var1)');

2. Reduce the variable lengths

Long character variables take up more memory. Decreasing the length to their minimum required value will help reduce memory usage. Use the %squeeze() macro to automatically reduce all variables to their minimum required size before loading. You can find that macro here.

%squeeze(mydata, mydata_smaller);

3. Adjust the hashexp option

hashexp helps improve performance and reduce hash collisions. Larger values of hashexp will increase memory usage but may improve performance. Smaller values will reduce memory usage. I recommend reading the link above and also looking at the link at the top of this post by sasnrd to get an idea of how it will affect your join. This value should be sized appropriately depending on the size of your table. There's no hard and fast answer as to what value you should use, my recommendation is as big as your system can handle.

dcl hash h1(dataset: 'mydata', hashexp:2);

4. Allocate more memory to your SAS session

If you often run out of memory with your hash tables, you may have too low of a memsize. Many machines have plenty of RAM nowadays, and SAS does a really great job of juggling multiple hard-hitting SAS sessions even on moderately equipped machines. Increasing this can make a huge difference, but you want to adjust this value as a last resort.

The default memsize option is 2GB. Try increasing it to 4GB, 8GB, 16GB, etc., but don't go overboard, like setting it to 0 to use as much memory as it wants. You don't want your SAS session to eat up all the memory on the machine if other users are also on it.

Temporarily setting it to 0 can be a helpful troubleshooting tool to see how much memory your hash object actually occupies if it's not running. But if it's your own machine and you're the only one using it, you can just go ham and set it to 0.

memsize can be adjusted at SAS invocation or within the SAS Configuration File directly (sasv9.cfg on 9.4, or SASV9_Option environment variable in Viya).

user_53427000

I have a fairly similar problem that I approached slightly differently.

First: all of what Stu says is good to keep in mind, regardless of the issue.

If you are in a situation though where you can't really reduce the character variable size (remember, all numerics are 8 bytes in RAM no matter what the dataset size, so don't try to shrink them for this reason), you can approach it this way.

  1. Build a hash table with key1 as key, key2 as data along with your actual data. Make sure that key1 is the "better" key - the one that is more fully populated. Rename Key2 to some other variable name, to make sure you don't overwrite your real key2.
  2. Search on key1. If key1 is found, great! Move on.
  3. If key1 is missing, then use a hiter object (hash iterator) to iterate over all of the records searching for your key2.

This is not very efficient if key2 is used a lot. Step 3 also might be better done in a different way than using a hiter - you could do a keyed set or something else for those records, for example. In my particular case, both the table and the lookup were missing key1, so it was possible to simply iterate over the much smaller subset missing key1 - if in your case that's not true, and your master table is fully populated for both keys, then this is going to be a lot slower.

The other thing I'd consider is abandoning hash tables and using a keyed set, or a format, or something else that doesn't use RAM.

Or split your dataset:

data haskey1 nokey1;
  set yourdata;
  if missing(key1) then output nokey1;
  else output haskey1;
run;

Then two data steps, one with a hash with key1 and one with a hash with key2, then combine the two back together.

Which of these is the most efficient depends heavily on your dataset sizes (both master and lookup) and on the missingness of key1.

user_16230070
Related