Counter that conditionally starts over/increments

[scottp]

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

      work.book2 (firstobs=2 keep=acct bgn rename=(acct=lead_acct bgn=lead_bgn));

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

[Reeza]

Have you looked into by group processing?

[scottp]

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

[Reeza]

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;

[ArtC]

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

retain counter1 0;

[scottp]

Excellent, thank you for your help!

[scottp]

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

[ArtC]

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;

[scottp]

Thank you!