Generate sequence numbers by subject for each term

[RAVI2000]

Hi all,

I would like t o generate sequence and sub-sequence numbers like below. Please help me out.

data have;
input subject @5 aedecod $ 5-42;
datalines;
001 Fatigue                              
001 Nausea                               
001 Vomiting                             
003 Asthenia                             
003 Hypothension                         
003 Thrombocytopenia                     
003 Thrombocytopenia                     
003 Thrombocytopenia                     
004 Anaemia                              
004 Blood creatinine increased           
004 Blood creatinine increased           
004 Blood creatinine increased           
004 Epistaxis                            
004 Epistaxis                            
004 Thrombocytopenia                     
004 Upper respiratory tract infection    
005 Alanine aminotransfrerase increased  
005 Alanine aminotransfrerase increased  
005 Alanine aminotransfrerase increased  
005 Aspartate aminotransfrerase increased
005 Aspartate aminotransfrerase increased
005 Aspartate aminotransfrerase increased
005 Blood creatinine increased           
005 Cellulitis                           
005 Cellulitis                           
005 Cellulitis                           
005 Chills                               
005 Chloroma                             
005 Cholelithiasis                       
005 Epistaxis                            
005 Pyrexia                              
005 Pyrexia                              
005 Subcutaneous haematoma               
005 Urinary Tract infection              
005 Urinary Tract infection              
;
run;

[Kurt_Bremser]

data want;
set have;
by subject aeterm;
retain seq sseq_n;
if first.subject
then seq = 1,
else seq + 1;
if first.aeterm
then sseq_n = 1;
else sseq_n + 1;
if first.aeterm ne last.aeterm then sseq = catx(".",seq,sseq_n);
drop sseq_n;
run;

Untested, posted from my tablet.

[RAVI2000]

Thank you for getting back. It is not working @Kurt_Bremser.
No error though, but not desired result.

[andreas_lds · Accepted solution]

Try

data want;
   set have;
   by subject aedecod;
   retain seq sseq_n;
   
   length sseq $ 10;
   
   if first.subject then do;
      seq = 0;
   end;
   
   if first.aedecod then do;
      seq = seq + 1;
      sseq_n = 0;
   end;
   
   if first.aedecod and not last.aedecod or sseq_n > 0 then do;
      sseq_n = sseq_n + 1;
      sseq = cats(seq, '.', sseq_n);
   end;

   drop sseq_n;
run;

[RAVI2000]

Thank you @andreas_lds

[RAVI2000]

The sorting is causing a little issue for sseq variable.
Can we get something like below?

sseq with numbers instead of decimal increment?

[Kurt_Bremser]

I see; this does it:

data want;
set have;
by subject aedecod;
retain seq sseq_n;
if first.subject then seq = 0;
if first.aedecod
then do;
  seq + 1;
  sseq_n = 1;
end;
else sseq_n + 1;
if first.aedecod + last.aedecod ne 2 then sseq = catx(".",seq,sseq_n);
drop sseq_n;
run;

[Ksharp]

You want make a unschedule visit number ?

data have;
input subject @5 aedecod $ 5-42;
datalines;
001 Fatigue                              
001 Nausea                               
001 Vomiting                             
003 Asthenia                             
003 Hypothension                         
003 Thrombocytopenia                     
003 Thrombocytopenia                     
003 Thrombocytopenia                     
004 Anaemia                              
004 Blood creatinine increased           
004 Blood creatinine increased           
004 Blood creatinine increased           
004 Epistaxis                            
004 Epistaxis                            
004 Thrombocytopenia                     
004 Upper respiratory tract infection    
005 Alanine aminotransfrerase increased  
005 Alanine aminotransfrerase increased  
005 Alanine aminotransfrerase increased  
005 Aspartate aminotransfrerase increased
005 Aspartate aminotransfrerase increased
005 Aspartate aminotransfrerase increased
005 Blood creatinine increased           
005 Cellulitis                           
005 Cellulitis                           
005 Cellulitis                           
005 Chills                               
005 Chloroma                             
005 Cholelithiasis                       
005 Epistaxis                            
005 Pyrexia                              
005 Pyrexia                              
005 Subcutaneous haematoma               
005 Urinary Tract infection              
005 Urinary Tract infection              
;
run;

data temp;
 set have;
 by subject  aedecod notsorted;
 if first.subject then seq=0;
 if first.aedecod then seq+1;
run;
data want;
 set temp;
 by subject  seq notsorted;
 if first.seq then n=0;
 n+1;
 sseq=input(cats(seq,'.',n),best.);
 if first.seq and last.seq then call missing(sseq);
 drop n;
run;

[RAVI2000]

Thank you for the response @Ksharp I am actually looking to output something different. I want both seq and sseq. And SSEQ without decimal increment(just numeric increment) as per latest requirement because of sort issue.

[RAVI2000]

I would like to output rows with aeout='Not-recovered/Not-resolved' where aeendtc is not missing.
When there are multiple records for same AEDECOD with in subject, then the last record with aeout="Not-recovered/NOt-resolved' with not missing aeendtc should output. 

data have;
input subject @5 aedecod $ 5-42 @43 aestdtn yymmdd10. @54 aeout $ 54-81 @80 aeendtn yymmdd10.;
format aestdtc aeendtc yymmdd10.;
datalines;
001 Fatigue                               2019-06-03 Not-recovered/Not-resolved .
001 Nausea                                2019-06-03 Not-recovered/Not-resolved .
001 Vomiting                              2019-06-03 Not-recovered/Not-resolved .
003 Asthenia                              2019-08-28 Recovered/Resolved         2019-08-28
003 Hypothension                          2019-06-25 Recovered/Resolved         2019-08-08
003 Thrombocytopenia                      2019-07-11 Recovered/Resolved         2019-07-23
003 Thrombocytopenia                      2019-07-18 Recovered/Resolved         2019-08-04
003 Thrombocytopenia                      2019-08-12 Not-recovered/Not-resolved 2019-08-12
004 Anaemia                               2019-07-10 Not-recovered/Not-resolved .
004 Blood creatinine increased            2019-08-19 Recovered/Resolved         2019-09-04
004 Blood creatinine increased            2019-09-02 Recovered/Resolved         2019-11-13
004 Blood creatinine increased            2019-11-11 Recovered/Resolved         2019-12-08
004 Epistaxis                             2019-01-21 Not-recovered/Not-resolved .
004 Epistaxis                             2019-11-25 Recovered/Resolved         2019-11-28
004 Thrombocytopenia                      2019-12-02 Not-recovered/Not-resolved .
004 Upper respiratory tract infection     2019-10-10 Recovered/Resolved         2019-10-13
005 Alanine aminotransfrerase increased   2019-08-14 Not-recovered/Not-resolved 2020-08-20
005 Alanine aminotransfrerase increased   2019-08-21 Recovered/Resolved         2020-09-06
005 Alanine aminotransfrerase increased   2019-09-07 Not-recovered/Not-resolved .
005 Aspartate aminotransfrerase increased 2019-08-14 Not-recovered/Not-resolved 2020-08-20
005 Aspartate aminotransfrerase increased 2019-08-21 Recovered/Resolved         2020-09-06
005 Aspartate aminotransfrerase increased 2019-09-07 Not-recovered/Not-resolved .
005 Blood creatinine increased            2019-05-29 Not-recovered/Not-resolved 2020-07-20
005 Cellulitis                            2020-07-01 Not-recovered/Not-resolved 2020-08-19
005 Cellulitis                            2020-08-20 Recovering/Resolving       2020-09-25
005 Cellulitis                            2020-08-26 Not-recovered/Not-resolved .
005 Chills                                2019-12-26 Recovered/Resolved         2019-12-27
005 Chloroma                              2020-04-30 Not-recovered/Not-resolved 2020-12-30
005 Cholelithiasis                        2020-08-18 Recovered/Resolved         2020-08-20
005 Epistaxis                             2019-12-17 Recovering/Resolving       2019-12-26
005 Pyrexia                               2020-08-16 Recovered/Resolved         2020-08-20
005 Pyrexia                               2020-08-20 Not-recovered/Not-resolved .
005 Subcutaneous haematoma                2020-01-24 Not-recovered/Not-resolved .
005 Urinary Tract infection               2020-08-20 Recovering/Resolving       2020-08-25
005 Urinary Tract infection               2020-08-26 Not-recovered/Not-resolved .
;
run;

[Kurt_Bremser]

What about aeout groups that have no "Not-recovered/Not-resolved" with a non-missing aeendtc? Should they all be omitted?

[Ksharp]

Can you post the output you want to see ?