DATA Step, Macro, Functions and more

Counter that conditionally starts over/increments

Reply
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

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

run;  

Thx!

acctbgnlead_acctlead_bgncounter1??counter 2??
1231/3/20121231/27/201211
1231/3/20121231/30/201212
1231/3/20121232/3/201213
1231/3/20121233/3/201214
1232/3/20121233/10/201221
1232/3/20121234/1/201222
1233/10/20124562/10/201331
4562/1/2013..41
Super User
Posts: 19,815

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: 19,815

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: 634

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_numstatusstat_bgnstat_endlag_stat_endfirst_bgn
123ACTV1/2/20122/1/2012.1/2/2012
123ACTV2/1/20126/2/20122/1/20121/2/2012
123ACTV8/3/201211/11/20126/2/20128/3/2012
123ACTV11/11/20121/1/201311/11/20128/3/2012
123ACTV1/1/20135/5/20131/1/20138/3/2012
444ACTV5/1/20138/1/20135/5/20135/1/2013
444ACTV10/1/201311/1/20138/1/201310/1/2013
444ACTV11/1/201312/2/201311/1/201310/1/2013
444ACTV12/2/201312/31/201312/2/201310/1/2013
555ACTV4/1/20125/1/201212/31/20134/1/2012
666ACTV6/1/20126/30/20125/1/20126/1/2012
777ACTV2/2/20122/15/20126/30/20122/2/2012
Valued Guide
Posts: 634

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!

Ask a Question
Discussion stats
  • 8 replies
  • 634 views
  • 0 likes
  • 3 in conversation