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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.