Hey tangentray,
_Null_ is right about the compound index thing. But if you have these 2 indexes already created and maintained and you're not allowed to create extra indexes, then i believe creating compound index of both the variables for just this thing/matching would be a problem for you.
If you're doing a lookup on a large dataset ( data B) then i believe format technique of lookup can help( check out paper on SGF 2009 :: Proc Format, a Speedy Alternative to Sort Sort Merge ::
http://support.sas.com/resources/papers/proceedings09/064-2009.pdf )
Back when i was learning about the set with Key= option, I wrote something that matches with your multiple index problem ( though not exactly the solution you would want as KEY= Multiple simple indexS is not wht i wrote, but KEY=single simple index with multiple SET statement ). I believe this code would increase the CPU time( if you've read the set ith key= theory you would knw why the increase in time), but the increase is depended on the data and the index variables being sorted/unsorted before they were made index.
data master(index=(PartNumber quantity ));
input PartNumber quantity;
datalines;
100 10
200 20
300 30
400 40
500 50
;
data description(index=(PartNumber quantity ));
input PartNumber quantity partdescription $;
datalines;
400 40 Nuts
300 30 Bolts
200 10 Screws
600 90 Washers
500 50 Bashers
;
data extract;
set description; /* smaller dsn, transaction dsn*/
set master(drop=quantity) key=PartNumber ; /* larger dsn*/
length errormessage $200.;
drop errormessage;
select (_iorc_);
when(%sysrc(_sok)) do; /* A match was found */
PartNumberX=PartNumber;
set master key=quantity ;
select (_iorc_);
when(%sysrc(_sok)) do; /* A match was found */
if PartNumberx=PartNumber then
output;
else delete;
end;
when (%sysrc(_dsenom)) do; /* No match was found */
_error_ = 0;
end;
otherwise do;
errormessage = iorcmsg();
put "ATTENTION: unknown error condition: "
errormessage;
end;
end;
end;
when (%sysrc(_dsenom)) do; /* No match was found */
_error_ = 0;
end;
otherwise do;
errormessage = iorcmsg();
put "ATTENTION: unknown error condition: "
errormessage;
end;
end;
run;
proc print;run;
Thanks!