Counter field that can interrupt and resume count based on a condition

[greg6363]

In my code, I have created a counter field that tracks the number of iterations for an event within a given account. Now I am trying to create a second counter field that can stop and restart the iteration count if a given event fails to occur in a given observation.  Let me spell out the data set and the code:

AcctNo    Date    Result    Counter

2111      5/7/20    Success   1

2111      5/14/20  Success   2

2111      5/21/20  Success   3

2111      5/28/20  Success   4

2111      6/4/20    Failure      0

2111      6/11/20  Success   5

2112      5/7/20    Success   1

2112      5/14/20  Failure      0

2112      5/21/20  Success   2

When the value "Failure" is in the result field, I want the counter to stop and the field filled with a zero value.  If the next observation shows "Success" in the result, the counter should resume its count where it left off before the "Failure" instance.  The process should repeat itself for each account number value.

I wrote the following code:

data test1;

set masterdata:

by AcctNo;

if first.AcctNo

then event_int = 1;

retain event_int;

if Result = 'Failure' then event_int = 0
else event_int + 1;

run;

Unfortunately, it is not storing the previous counter value before the interruption so when I resume the counter, it starts back at 1.

Any feedback would be greatly appreciated.  Thanks.

[novinosrin]

HI @greg6363 -

data have;
 input (AcctNo    Date    Result) ($);*    Counter;
 cards;
2111      5/7/20    Success   1

2111      5/14/20  Success   2

2111      5/21/20  Success   3

2111      5/28/20  Success   4

2111      6/4/20    Failure      0

2111      6/11/20  Success   5

2112      5/7/20    Success   1

2112      5/14/20  Failure      0

2112      5/21/20  Success   2
;

data want;
 if _n_=1 then do;
  dcl hash h(suminc:'_iorc_');
  h.definekey('Result');
  h.definedone();
 end;
 retain _iorc_ 1;
 do until(last.acctno);
  set have;
  by acctno;
  h.ref();
  h.sum(sum:counter);
  if Result='Failure' then counter=0;
  output;
 end;
 h.clear();
run;

[mkeintz · Accepted solution]

Store the count in a retained variable (_n below).  If the observation in hand is a success, assign that value to COUNTER, otherwise set COUNTER to 0:

data test1 (drop=_n:);
  set masterdata;
  by acctno;
  if first.acctno then _n=0;
  _n+result='Success';
  counter=ifn(result='Success',_n,0);
run;

[greg6363]

The code works to a T. All set. Thanks.