@FreelanceReinh cool stuff 🙂
Here are my results on the large data. Just for fun I added a Hash Object approach
data one(drop=i j);
length type $8 mvalue $12;
call streaminit(27182818);
do i=1 to 1e7;
type=put(i, hex8.);
do j=1 to rand('integer',10);
mvalue=choosec(rand('integer',4),'closed','Progress','Closed','other');
output;
end;
end;
run; /* 54995649 obs. */
/* 10.72 sec */
data want1(drop=flag);
do until(last.type);
set one;
by type;
if mvalue='Progress' then flag=1;
end;
do until(last.type);
set one;
by type;
if flag then mvalue='Progress';
output;
end;
run;
/* 11.8 sec */
data want2(drop=_m);
merge one(rename=(mvalue=_m))
one(where=(mvalue='Progress') in=p);
by type;
if ~p then mvalue=_m;
run;
/* 37.3 sec */
proc sql;
create table want3 as
select type, case when max(mvalue='Progress')
then 'Progress'
else mvalue
end as mvalue
from one
group by type;
quit;
/* 14.2 sec */
data want4;
if _N_ = 1 then do;
declare hash h(dataset:"one(where=(Mvalue='Progress'))", hashexp:20);
h.defineKey('type');
h.defineDone();
end;
set one;
if h.check()=0 then Mvalue='Progress';
run;
... View more