I have found similiar problems with SQL joins of large data sets. While the data step is usually faster, merging requires that the joined data be presorted. I have have began experimenting using Hash tables in the data step to join records. While I'm not comfortable fully explaining them, they have provided the performance enhancements that I wanted. There are many good SAS Global forum papers that explain and give examples. Just search hash tables in tech support search.
You can test this code. If dataset people is not very large then should work very fast.
data responders(keep=id_one id_two id_final index=(cr1=(id_one id_two)));
set responders(rename=(final_id_one=id_one final_id_two=id_two));
set responders key=cr1/unique;
when(%sysrc(_sok)) do; end;
when(%sysrc(_dsenom)) do; id_final= . /* if id_final is char then should be id_final=''*/; _error_ = 0; end;
put 'ERROR_: Unexpected value for _IORC_= ' _iorc_ ' Program terminating. Data set accessed is responders';
put _all_; _error_ = 0; stop; end;
You could investigate using a hash table in a data step.
While the syntax is unusual at first glance, it is quite easy to grasp.
Hash tables are fast as they are loaded in memory, and don't require any prior sorting.
The pseudo code looks like:
data matching ;
if _N_=1 then load hash table;
set base table;