Hash is the fastest method for looking up data, since the data is read just once from disk and then loaded into memory. There is also no need to sort or index that table in advance. The most important limitation is that memory available to SAS is enough to fit the hash table. There are situations a PROC SQL might be as fast as a self coded hash lookup, because the SQL optimizer will do a hashjoin if it determines the lookup table fits into memory. In-database also also usually faster than SAS joins and might even be faster than hash lookup. With in-database joins the SAS system does not need to bring over all data from the database to SAS first, before the join can be excuted. A join done in-database will only transfer the results, resulting in less movement of data. The SQL optimizer tries to pass as much processing as possible to the database, but there are situations it does this less optimal than you can do by hand.
... View more