topic Find consecutive values count of NOT DONE in SAS Programming

Find consecutive values count of NOT DONE

draroda — Sat, 15 May 2021 15:03:13 GMT

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.

Re: Find consecutive values count of NOT DONE

novinosrin — Sat, 15 May 2021 15:28:19 GMT

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

Re: Find consecutive values count of NOT DONE

Kurt_Bremser — Sat, 15 May 2021 19:05:19 GMT

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.