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 |
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;
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;
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 16. 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.