Looking at the next variable number and assigning a new field

[lsehlola]

Hi All

I have a table with duplicate key. This key can occur any number of times as per below table. 

We need to look at the highest number in ATM_ACNT_S. Where ATM_ACNT_S - 4, add one to the highest number of ATM_ACNT_S and populate ATM_ACNT_S_new.

if ATM_ACNT_S ne 4, then the ATM_ACNT_S_NEW = ATM_ACNT_S else if ATM_ACNT_S = 4 then ATM_ACNT_S_NEW = Highest number of ATM_ACNT_S + 1

CID	ATM_ACNT_S	ATM_ACNT_S_NEW	COUNTER
929069935	11	11	1
929069935	4	16	2
929069935	4	17	3
929069935	4	18	4
929069935	12	12	5
929069935	13	13	6
929069935	14	14	7
929069935	15	15	8

[PaigeMiller]

data have;
input CID ATM_ACNT_S COUNTER;
cards;
929069935	11	1
929069935	4	2
929069935	4	3
929069935	4	4
929069935	12	5
929069935	13	6
929069935	14	7
929069935	15	8
;

proc summary nway data=have(where=(atm_acnt_s^=4));
    class cid;
    var atm_acnt_s;
    output out=_max_(drop=_:) max=/autoname;
run;
data want;
    merge have _max_;
    by cid;
    if atm_acnt_s=4 then do;
        add+1;
        atm_acnt_s_new=atm_acnt_s_max+add;
    end;
    else atm_acnt_s_new=atm_acnt_s;
    drop add;
run;

[mkeintz]

If your data are sorted by CID, then a data step with two passes through each CID can do what you want:

data have;
input CID	ATM_ACNT_S COUNTER;
cards;
929069935	11	1
929069935	4	2
929069935	4	3
929069935	4	4
929069935	12	5
929069935	13	6
929069935	14	7
929069935	15	8
run;
data want (drop=_:);
  set have (in=firstpass)
      have (in=secondpass);
  by cid;
  retain _s_max .;
  if first.cid then _s_max=.;
  if firstpass then _s_max=max(_s_max,atm_acnt_s);
  if secondpass;
  if atm_acnt_s^=4 then atm_acnt_s_new=atm_acnt_s;
  else do;
    _s_max+1;
    atm_acnt_s_new=_s_max;
  end;
run;

BTW, if the data are grouped by CID, but not in ascending or descending order, you can still use the above approach:

data want (drop=_:);
  do until (last.cid);
    set have;
    by cid notsorted;
    _s_max=max(_s_max,atm_acnt_s);
  end;

  do until (last.cid);
    set have;
    by cid notsorted;
    if atm_acnt_s^=4 then atm_acnt_s_new=atm_acnt_s;
    else do;
      _s_max+1;
      atm_acnt_s_new=_s_max;
    end;
    output;
  end;
run;