BookmarkSubscribeRSS Feed
scottp
Calcite | Level 5

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
8 REPLIES 8
Reeza
Super User

Have you looked into by group processing?

scottp
Calcite | Level 5

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

Reeza
Super User

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
Rhodochrosite | Level 12

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

retain counter1 0;

scottp
Calcite | Level 5

Excellent, thank you for your help!

scottp
Calcite | Level 5

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
ArtC
Rhodochrosite | Level 12

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
Calcite | Level 5

Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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