## Counter that conditionally starts over/increments

Occasional Contributor
Posts: 7

# Counter that conditionally starts over/increments

Hello,

Please see the table, below. How can I create 2 counters in a data step:

Counter 1- increments when either acct = next acct AND bgn <> to lead_bgn; OR, when acct <> lead_acct

Counter 2- increments until acct = lead_acct AND bgn <> lead_bgn; OR, acct <> lead_acct.....when it hits either condition the counter resets to 1

Code so far (to get lead_acct and lead_bgn:

proc sort data=work.book1 out=book2;

by acct bgn;

run;

data book3;

merge work.book2

run;

Thx!

 acct bgn lead_acct lead_bgn counter1?? counter 2?? 123 1/3/2012 123 1/27/2012 1 1 123 1/3/2012 123 1/30/2012 1 2 123 1/3/2012 123 2/3/2012 1 3 123 1/3/2012 123 3/3/2012 1 4 123 2/3/2012 123 3/10/2012 2 1 123 2/3/2012 123 4/1/2012 2 2 123 3/10/2012 456 2/10/2013 3 1 456 2/1/2013 . . 4 1
Super User
Posts: 23,663

## Re: Counter that conditionally starts over/increments

Have you looked into by group processing?

Occasional Contributor
Posts: 7

## Re: Counter that conditionally starts over/increments

Yes, I tried by grouping in the data set, but gave up.

Super User
Posts: 23,663

## Re: Counter that conditionally starts over/increments

data want;

set have;

by acct bgn;

retain counter1=0;

if first.bgn then counter1+1;

if first.bgn then counter2=1;

else counter2+1;

run;

Valued Guide
Posts: 653

## Re: Counter that conditionally starts over/increments

@Reeza, slight typo (no = in the RETAIN statement)

retain counter1 0;

Occasional Contributor
Posts: 7

## Re: Counter that conditionally starts over/increments

Excellent, thank you for your help!

Occasional Contributor
Posts: 7

## Re: Counter that conditionally starts over/increments

Art,

What if I wanted to retain the stat_bgn to a new variable called first_bgn (below), that would retain until the lag_stat_end <> stat_bgn?

 cust_acct_num status stat_bgn stat_end lag_stat_end first_bgn 123 ACTV 1/2/2012 2/1/2012 . 1/2/2012 123 ACTV 2/1/2012 6/2/2012 2/1/2012 1/2/2012 123 ACTV 8/3/2012 11/11/2012 6/2/2012 8/3/2012 123 ACTV 11/11/2012 1/1/2013 11/11/2012 8/3/2012 123 ACTV 1/1/2013 5/5/2013 1/1/2013 8/3/2012 444 ACTV 5/1/2013 8/1/2013 5/5/2013 5/1/2013 444 ACTV 10/1/2013 11/1/2013 8/1/2013 10/1/2013 444 ACTV 11/1/2013 12/2/2013 11/1/2013 10/1/2013 444 ACTV 12/2/2013 12/31/2013 12/2/2013 10/1/2013 555 ACTV 4/1/2012 5/1/2012 12/31/2013 4/1/2012 666 ACTV 6/1/2012 6/30/2012 5/1/2012 6/1/2012 777 ACTV 2/2/2012 2/15/2012 6/30/2012 2/2/2012
Valued Guide
Posts: 653

## Re: Counter that conditionally starts over/increments

Adding a bit of logic should do the trick.  Remember to retain the new variable that you want to carry forward.

data have;

input cust_acct_num \$ status \$ stat_bgn :mmddyy10. stat_end  :mmddyy10. ;

datalines;

123 ACTV 1/2/2012   02/1/2012

123 ACTV 2/1/2012   06/2/2012

123 ACTV 8/3/2012   11/11/2012

123 ACTV 11/11/2012 01/1/2013

123 ACTV 1/1/2013   05/5/2013

444 ACTV 5/1/2013   08/1/2013

444 ACTV 10/1/2013  11/1/2013

444 ACTV 11/1/2013  12/2/2013

444 ACTV 12/2/2013  12/31/2013

555 ACTV 4/1/2012   05/1/2012

666 ACTV 6/1/2012   06/30/2012

777 ACTV 2/2/2012   02/15/2012

run;

data want;

set have;

by cust_acct_num;

lag_stat_end = lag(stat_end);

retain first_bgn .;

if first.cust_acct_num or lag_stat_end ne stat_bgn then first_bgn =stat_bgn;

format stat_bgn stat_end lag_stat_end first_bgn mmddyy10.;

run;

Occasional Contributor
Posts: 7

## Re: Counter that conditionally starts over/increments

Thank you!

Discussion stats
• 8 replies
• 946 views
• 0 likes
• 3 in conversation