topic Creating a counter variable that resets to zero based on the value of another field in SAS Programming

Creating a counter variable that resets to zero based on the value of another field

greg6363 — Thu, 08 Apr 2021 20:58:42 GMT

I need to create a counter variable field (rtn_int) that resets to zero when a field value condition is not met. Here is the data set:

AccountNumber PmtNumber RtnType

1001 1 Non-R01

1001 2 Non-R01

1001 3 R01

1001 4 Non-R01

1001 5 R01

1001 6 R01

1002 1 R01

1002 2 Non-R01

1003 1 Non-R01

1003 2 R01

1004 1 Non-R01

1005 1 R01

If the RtnType field has a R01 value, then the Rtn_Int field should activate the count.

If the RtnType field has a Non-R01 value, then the Rtn_Int field should stop the count and reset to zero

and restart the count for the respective account number when the next R01 value appears in the subsequent record(s).

This should be the layout of the output file:

AccountNumber PmtNumber RtnType Rtn_Int

1001 1 Non-R01 0

1001 2 Non-R01 0

1001 3 R01 1

1001 4 Non-R01 0

1001 5 R01 1

1001 6 R01 2

1002 1 R01 1

1002 2 Non-R01 0

1003 1 Non-R01 0

1003 2 R01 1

1004 1 Non-R01 0

1005 1 R01 1

This was the code I wrote but the counter continues the count and does not reset to zero in order to start a new count:

data rtns_list_final;

set rtns_list;

by accountnumber;

if first.accountnumber then _n=0;

_n+RtnType='R01';

rtn_int = ifn(RtnType='R01',_n,0);

run;

Any assistance would be greatly appreciated. Thanks.

Re: Creating a counter variable that resets to zero based on the value of another field

PGStats — Thu, 08 Apr 2021 21:13:25 GMT

It seems like you need to replace

if first.accountnumber then _n=0;

with

if first.accountnumber or RtnType='R01' then _n=0;

Re: Creating a counter variable that resets to zero based on the value of another field

greg6363 — Thu, 08 Apr 2021 21:29:10 GMT

Unfortunately, that line of code (if first.accountnumber or RtnType='R01' then _n=0;) only produces the 1 value.
In the event of successive records with a R01 value, I need to have the counter to continue (1,2,3, ... n) until it sees a 'Non-R01' value when the counter reverts to zero.

Re: Creating a counter variable that resets to zero based on the value of another field

ballardw — Thu, 08 Apr 2021 21:48:00 GMT

This appears to work for the provided example.

data want;
   set have;
   by accountnumber;
   if first.accountnumber then  _n=0;
   if RtnType='Non-R01' then _n =0;
   else _n +1;

run;

Re: Creating a counter variable that resets to zero based on the value of another field

greg6363 — Thu, 08 Apr 2021 22:06:34 GMT

The code didn't work for me. You left out the rtn_int field.

Re: Creating a counter variable that resets to zero based on the value of another field

ballardw — Thu, 08 Apr 2021 23:59:08 GMT

@greg6363 wrote:
The code didn't work for me. You left out the rtn_int field.

If you can't figure out that the _n is the rtn_int field I predict a long hard road learning any programming.

Re: Creating a counter variable that resets to zero based on the value of another field

greg6363 — Fri, 09 Apr 2021 00:13:41 GMT

My apologies, ballardw. I had a brain fart. I mistakenly copied a drop statement which removed the column which is why I didn't see it in my output. I'm looking at the column and the values are correct for the condition. I can use a rename statement to label the column. Thanks for setting me straight. Much appreciated.