Hi ,
I have extra request for RANK variable with below scenario.
As we develop RANK value up till now, This is a new scenario, I would require highlighted value for obs. 7 and 8 as 5 and not 1
like this...
Reason: while deriving RANK value for obs. 7, client wanted to check latest of LLT = urea value, which is obs. no. 4 and AENDT of obs. no. 4 is not equal to ASTDT of obs. no. 7, so we need to keep adding RANK +1.
currently while deriving RANK value for obs. 7, we are considering obs. no. 3.
can we modify our existing code for this requirement.
sorry to trouble you with this again.
I am also attaching the datalines for this.
data test;
input @1 usubjid $3.
@5 ae $8.
@14 llt $8.
@23 astdt date9.
@33 aendt date9.
;
format astdt aendt date9.;
datalines;
410 amino alan 19JUL2022 08AUG2022
410 amino aspar 01AUG2022 08AUG2022
410 Toxicity urea 26JUL2022 18AUG2022
410 Toxicity urea 02AUG2022 15AUG2022
410 Toxicity prot 08AUG2022 21SEP2022
410 Toxicity creat 11AUG2022 22AUG2022
410 Toxicity urea 18AUG2022 18AUG2022
410 Toxicity urea 18AUG2022 02SEP2022
410 Toxicity minuria 29AUG2022 21SEP2022
410 Toxicity urea dec 15SEP2022 21SEP2022
720 Toxicity estim 27DEC2022 17JAN2023
720 Toxicity urea 03JAN2023 17JAN2023
720 Toxicity creat 10JAN2023 24JAN2023
720 Toxicity estim 17JAN2023 28FEB2023
720 Toxicity urea 31JAN2023 14FEB2023
720 Toxicity creat 31JAN2023 14FEB2023
720 Toxicity urea 21FEB2023 28FEB2023
720 Toxicity estim 28FEB2023 21MAR2023
;
run;
And what's your current logic to calculate rank? Please share this code as well plus some explanation.
Hi @Patrick
I need to create RANK variable in the dataset shown below (for each USUBJID and AE).
The input variables are USUBJID, ASTDT, AENDT, AE, LLT.
dataset sorted by USUBJID , AE , ASTDT and AENDT.
if the AENDT and LLT of the prior AE is not the ASTDT and LLT of the next AE, then we need to create RANK as +1.
If the ASTDT and LLT of the next AE is the AENDT and LLT of the previous AE record then RANK remains the same.
This is the code , what we are using for now
data want;
set test;
by usubjid ae;
length rank 8 maxrank 8;
retain rank;
if _n_=1 then do;
declare hash end ();
end.definekey("aendt", "llt");
end.definedata("rank");
end.definedone();
end;
if first.usubjid then do;
rc=end.clear();
maxrank=1;
rank=1;
rc=end.add();
end;
else do;
if end.find(key:astdt, key:llt) ne 0 then
do;
maxrank + 1;
rank=maxrank;
rc=end.add();
end;
else do;
if end.check() ne 0 then
rc=end.add();
end;
end;
drop rc maxrank;
proc print;
run;
Looks like you want to use ae as an additional key for the hash.
Hi @Kurt_Bremser ,
I have made the below changes, but it is not working.
could you please help with this.
data want;
set test;
by usubjid ae;
length rank 8 maxrank 8;
retain rank;
if _n_=1 then do;
declare hash end ();
end.definekey("ae", "aendt", "llt");
end.definedata("rank");
end.definedone();
end;
if first.ae then do;
rc=end.clear();
maxrank=1;
rank=1;
rc=end.add();
end;
else do;
if end.find(key: ae, key:astdt, key:llt) ne 0 then
do;
maxrank + 1;
rank=maxrank;
rc=end.add();
end;
else do;
if end.check() ne 0 then
rc=end.add();
end;
end;
drop rc maxrank;
run;
Since I now played around with the code, it became obvious that we need only keep one entry per llt in the hash, but with the date as data, and then compare the dates. This led me to this code:
data want;
set test;
by usubjid ae notsorted; /* notsorted because the lowercase a comes after uppercase T */
length rank 8 maxrank 8;
retain rank maxrank; /* you forgot to retain maxrank */
if _n_ = 1
then do;
declare hash end ();
end.definekey("llt");
end.definedata("_aendt","rank");
end.definedone();
end;
if first.ae
then do;
rc = end.clear();
maxrank = 1;
rank = 1;
rc = end.add();
end;
else do;
if end.find(key:llt) ne 0
then do;
maxrank + 1;
rank = maxrank;
rc = end.add();
end;
else do;
if _aendt ne astdt
then do;
maxrank + 1;
rank = maxrank;
_aendt = aendt;
rc = end.replace();
end;
else do;
_aendt = aendt;
rc = end.replace();
end;
end;
end;
drop rc maxrank _aendt;
run;
Hi @Kurt_Bremser ,
Thanks, it worked 🙂
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.