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 |
Have you looked into by group processing?
Yes, I tried by grouping in the data set, but gave up.
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;
@Reeza, slight typo (no = in the RETAIN statement)
retain counter1 0;
Excellent, thank you for your help!
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 |
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;
Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.