Need Help for Sequence Loop

[sas_newbie94]

Hi All,

Would require assistance to achieve the following loop to create a NEW sequence column based on a flag.

Please find screenshot for reference. For each account number, I would like to RETAIN the first sequence. Meaning for accno 900, first sequence to retain is 40. For acc 991, first sequence to retain is 20. From there, it will add an incremental of +1 all the way until it hits a flag for reset with the value of 1. If it hits a value of 1 in the flag, it will reset the sequence and it will start from 1 and then add an incremental of +1 subsequently.

data example;
input accno old_sequence flag_to_reset new_sequence;
datalines;
900 40   40
900 41   41
900 42   42
900 43   43
900 44 1 1
900 45   2
900 46   3
900 47   4
900 48   5
991 20   20
991 21   21
991 22 1 1
991 23   2
991 24   3
991 25   4
991 26   5

;
run;

[Ksharp]

data example;
input accno old_sequence flag_to_reset ;
datalines;
900 40 .  40
900 41 .  41
900 42  . 42
900 43  . 43
900 44 1 1
900 45 .  2
900 46 .  3
900 47 .  4
900 48 .  5
991 20 .  20
991 21 .  21
991 22 1 1
991 23  . 2
991 24  . 3
991 25  . 4
991 26  . 5
;
run;
data want;
 set example;
 by accno;
 if first.accno then new_sequence=old_sequence-1;
 if flag_to_reset then new_sequence=0;
 new_sequence+1;
run;

[PaigeMiller]

data want;
    set example;
    by accno;
    if first.accno then new_sequence=old_sequence;
    if not first.accno and not flag_to_reset then new_sequence+1;
    if flag_to_reset then new_sequence=1;
run;

[sas_newbie94]

Hi both @PaigeMiller  @Ksharp ,

Thanks for the replies. Both codes worked!

However I have one more additional criteria that I need to look out for when recalculating the sequence.

If an account number does not have an AMT or AMT that is showing 0, we do not use back the old sequence column despite it being the FIRST.ACCNO. Pls refer to accno 900 for this scenario. Instead, at the point where there's an AMT we will have to recalculate the new_sequence and start from 1. Initially I thought I can omit AMT = 0 entirely from the data set, but take note that at subsequent rows if there's 0 AMT, we still retain the new_sequence count from above. Pls refer to highlighted in red text for 0 Amt for accno 900. It will still continue counting. ONLY at the starting point for each Accno, we need to check for the AMT value whether it's showing 0. If it's 0, we need to start counting at the instance when there's a AMT value.

For accno 991, we can use back the old sequence since it's the FIRST.ACCNO and there's an $ AMT to that row, so we can use back the old sequence and start flagging from there.

Prior checks for "Flag to reset" condition still exist.

data example;
input accno old_sequence flag_to_reset AMT ;
datalines;
900 40 . 0
900 41 . 0 
900 42  . 100
900 43  . 100
900 44 1 100
900 45 . 100
900 46 . 100
900 47 . 0 
900 48 . 0
991 20 . 100
991 21 . 100
991 22 1 100
991 23  . 100
991 24  . 100
991 25  . 100
991 26  . 100
;
run;

[PaigeMiller]

I did this using a RETAIN statement, although I'm sure there are other ways.

data want;
    set example;
    retain new_sequence;
    by accno;
    if first.accno and amt=0 then new_sequence=.;
    if first.accno and amt>0 then new_sequence=old_sequence;
    if not first.accno and not flag_to_reset then new_sequence=sum(new_sequence,1);  
    if flag_to_reset then new_sequence=1;
run;

[Ksharp]

data example;
input accno old_sequence flag_to_reset AMT ;
datalines;
900 40 . 0
900 41 . 0 
900 42  . 100
900 43  . 100
900 44 1 100
900 45 . 100
900 46 . 100
900 47 . 0 
900 48 . 0
991 20 . 100
991 21 . 100
991 22 1 100
991 23  . 100
991 24  . 100
991 25  . 100
991 26  . 100
;
run;
data want;
 set example;
 by accno;
 retain flag ;
 if first.accno then do;
   new_sequence=old_sequence-1;
   if amt=0 then flag=1;
 end;
 if amt ne 0 then flag=0;

 if flag_to_reset then new_sequence=0;
 new_sequence+1;
 if flag then  new_sequence=.;
run;