Find consecutive values count of NOT DONE

[draroda]

Hi All,

Can you please help me on how to find consecutive values count of NOT DONE? I have one dataset as below.

data check;
infile datalines missover;
input subject $3. visitn avalc $10. ;
datalines;
101 1 NOT DONE
101 2 DONE
101 3 NOT DONE
101 4 DONE
101 5 NOT DONE
101 6 NOT DONE
101 7 NOT DONE
101 8 DONE
101 9 NOT DONE
;

I need to find how many records have consecutive NOT DONE for visits.From above data , i am expecting final count as 3 as NOT DONE occured on each consecutive visit 5,6 and 7. 

Thanks in advance.

[novinosrin]

Hi @draroda  The sage @ballardw  answered a similar question explaining the use case of "NOT SORTED" option in BY GROUP processing so beautifully. I recommend reading his posts. So-

data check;
infile datalines missover;
input subject $3. visitn avalc $10. ;
datalines;
101 1 NOT DONE
101 2 DONE
101 3 NOT DONE
101 4 DONE
101 5 NOT DONE
101 6 NOT DONE
101 7 NOT DONE
101 8 DONE
101 9 NOT DONE
;

data want;
 do until(last.avalc);
  set check;
  by subject avalc notsorted;
  consecutive=sum(consecutive,1);
 end;
 do until(last.avalc);
  set check;
  by subject avalc notsorted;
  if consecutive>1 then output;
 end;
run;

 Btw, I am not quite all that good in explaining besides the fact I am the laziest person on earth however by all means I recommnend reading posts by group of marvels -Sir @Tom , @FreelanceReinh  etc. to gain speed

[Kurt_Bremser]

Try this:

data want;
set check;
by subject visitn; /* visitn purely to force correct order */
retain count _count;
if first.subject
then do;
  count = 0;
  _count = 0;
end;
if avalc = "DONE"
then do;
  count = max(count,_count);
  _count = 0;
end;
else _count + 1;
if last.subject;
keep subject count;
run;

Untested, posted from my tablet.