I don't think that avoiding proc sort improves the runtime. I got the following times, when have had 1000000 obs (100k ids, 10 obs each):
original code: duration=0.265001
suggestion by @s_lassen: duration=1.282
summary+transpose+hash: duration=0.859
Code of the hash-approach:
proc summary data=have nway;
class id;
var value;
output out= work.top max= idgroup(max(value) out[3] (value)=maxvalue );
run;
proc transpose data=work.top(keep= id maxvalue:) out=work.transposed(rename=(col1 = value)) name=position;
by id;
var maxvalue:;
run;
data want;
set have;
if _n_ = 1 then do;
if 0 then set work.transposed;
declare hash h(dataset: 'work.transposed');
h.defineKey('id', 'value');
h.defineData('position');
h.defineDone();
end;
if h.find() = 0 then do;
top = input(compress(position,, 'kd'), 1.);
end;
drop position;
run;
... View more
