topic Re: Counter field that can interrupt and resume count based on a condition in SAS Programming

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

greg6363 — Wed, 18 Nov 2020 22:28:04 GMT

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.

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

novinosrin — Wed, 18 Nov 2020 22:38:19 GMT

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;

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

mkeintz — Thu, 19 Nov 2020 00:14:23 GMT

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;

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

greg6363 — Thu, 19 Nov 2020 05:42:14 GMT

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