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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: