topic Re: Counter that conditionally starts over/increments in SAS Programming

Counter that conditionally starts over/increments

scottp — Wed, 13 Feb 2013 16:28:44 GMT

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

Re: Counter that conditionally starts over/increments

Reeza — Wed, 13 Feb 2013 16:51:17 GMT

Have you looked into by group processing?

Re: Counter that conditionally starts over/increments

scottp — Wed, 13 Feb 2013 16:55:01 GMT

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

Re: Counter that conditionally starts over/increments

Reeza — Wed, 13 Feb 2013 18:17:49 GMT

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;

Re: Counter that conditionally starts over/increments

ArtC — Sat, 16 Feb 2013 07:39:17 GMT

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

retain counter1 0;

Re: Counter that conditionally starts over/increments

scottp — Tue, 19 Feb 2013 19:32:48 GMT

Excellent, thank you for your help!

Re: Counter that conditionally starts over/increments

scottp — Tue, 19 Feb 2013 19:32:59 GMT

Thank you!

Re: Counter that conditionally starts over/increments

scottp — Tue, 19 Feb 2013 20:28:57 GMT

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

Re: Counter that conditionally starts over/increments

ArtC — Tue, 19 Feb 2013 21:18:35 GMT

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;