Solved: Re: Generate sequence numbers by subject for each term

RAVI2000 · Posted 07-05-2022 12:47 AM

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;

andreas_lds · Posted 07-05-2022 01:27 AM

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;

View solution in original post

Kurt_Bremser · Posted 07-05-2022 12:59 AM

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

RAVI2000 · Posted 07-05-2022 01:12 AM

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

andreas_lds · Posted 07-05-2022 01:27 AM

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 · Posted 07-05-2022 01:59 AM

Thank you @andreas_lds

RAVI2000 · Posted 07-05-2022 09:37 AM

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 · Posted 07-05-2022 03:35 AM

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;

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Ksharp · Posted 07-05-2022 07:59 AM

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 · Posted 07-05-2022 09:39 AM

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 · Posted 07-05-2022 10:02 AM

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 · Posted 07-05-2022 11:40 AM

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

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Ksharp · Posted 07-06-2022 08:18 AM

Can you post the output you want to see ?

Classroom Training Available!