AE Programming query (2)

[abhinayingole]

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;

[Patrick]

And what's your current logic to calculate rank? Please share this code as well plus some explanation.

[abhinayingole]

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;

[Kurt_Bremser]

Looks like you want to use ae as an additional key for the hash.

[abhinayingole]

Hi @Kurt_Bremser ,

  How to add this additional key ? not able to get it.

Thanks,
Abhi

[Kurt_Bremser]

See here: DEFINEKEY Method 

and FIND Method 

[abhinayingole]

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;

[Kurt_Bremser]

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;

[abhinayingole]

Hi @Kurt_Bremser ,

Thanks, it worked 🙂