Difficult merge/join with huge datasets

[jdmarino]

Well, I figured out the failure: I added my options to the top of the .cfg file, but they were being reset (to the lower values) at the bottom of the file!  The hash version is running now, and clearly using nearly all the RAM.

[jakarman]

For this kind of failures you the reason there is a usermods_config file specified. All usermodifications should go in that one, not in the default one supplied by SAS.
This is a bad habit seen by oldies of the V8/V6 time where the default installation did not support that. A technical guy could add that functionality.

With 9.3 you can included/nest config files and it setup that way. 

[jdmarino]

Here are the timing results based on my real dataset:

SQL method: 5 hours

SET-BY method: 55 seconds  :smileygrin:

SET-POINT method: untried  (It seems to me to replicate SET-BY, but requires more coding)

HASH method: 2 hours  (too few keys compared to overall data causes near-linear searching)

I learned some new tricks.  Thanks to all who contributed ideas.

[Haikuo]

Thanks for the feedback! It is good to know that old-school data step still rocks if you know how to swing. Also, to my surprise, Proc Sort takes way less time than I expected.

Haikuo