joining data from large data sets

[emsmpa]

Hi community,

we would be interested in knowing what is the best way of joining data from large data sets (>10million records).

Options:

a) sort and merge

b) create a format and then apply it within a data step

c) hash table join

d) sql join

Which is the quickest and which is the least memory intensive. We frequently used a format (option b) but within some codes they are crashing as we didn't have enough memory.
We subsequently used hash joins instead.

Do you have a view on how large a format can be (in terms of number of records) before its better to try another method?

Thanks!

[TomKari]

1. You are correct about the FORMAT approach. Because it's in-memory, it will be very fast, but uses up a lot of memory and you may run out. So, it's the fastest but the MOST memory intensive, sigh.

2. I have limited experience with hash table joins, so won't comment.

3. If your data is in SAS datasets, I believe you'll see similar performance from a sort and merge and from a SQL join, as behind the covers SQL will need to sort both datasets, and that's the expensive part.

4. If your data is in a database, depending on circumstances you might get the best results from pushing a JOIN to the database engine. It's worth trying, see if it's better, worse, or your DBA comes after you with a gun.

5. If you can sort and keep both datasets in the sequence of your join key, that will very fast with either a join or a sort and merge (sort is usually optimized to be very fast if the data is almost in the correct sequence).

Tom

[Tom]

Really depends on what you are doing.  If you were able to do it with a FORMAT then it sounds like on the of tables is used to lookup a decoded value for a variable available in the other.  In that case you can maintain the lookup table with an INDEX and then use the SET statement with the KEY= option to lookup the decode variable (or variables).

It might be possible the PROC SQL could optimize this for your with you having to do anything special in the code.

Whether to sort the other table depends on how it will be used.  But normally its sort variables are different than the variables needed to lookup in the other table.

For example you could have pharmacy claims sorted by patient id and date and want to lookup the drug name from the drugcode included in the claim record.

proc sql ;

create table new as select a.*,b.drugname

  from claims a left join drugs b

  on a.drugcode = b.drugcode

  order by a.patient,a.claimdt

;

quit;

[emsmpa]

Thanks to all of you

Helped us a lot.