From short to long format for SDTM programming

[NewUsrStat]

Hi guys, 

suppose to have the following table (I received as it is): 

data DB;
  input ID :$20. Start :$20. End :$20. TypeTransfusion1 :$20. TypeTransfusion2 :$20. TypeTransfusion3 :$20.; 
cards;
0001 2024-09-01 2024-10-01  RBC  Whole blood     .    
0001 2024-09-01 2024-10-01   .        .       Platelet
0002 2016-NK-NK 2016-NK-NK   .    Whole blood Platelet
0002 2016-05-02 2016-05-02  RBC Whole blood      .      
0002 2016-05-05 2016-05-04   .        .       Platelet
0003    .            .       .        .          .    
0003    .            .       .        .          .   
0004 2024-09-01 2024-10-01 RBC Whole blood       .   
0004 2024-09-01 2024-10-01  .         .       Platelet
0005 2025-11-03 2025-11-23  .   Whole blood      .     
0005 2025-11-03 2025-11-14 RBC       .           .  
; 
run;

I need to transform it in long format to be compliant with PR programming of SDTM (CDISC). 

Can anyone help me please? 

I used proc transpose on each of TypeTransfusion* variable (to finally merge the outputs) but without success because for each ID the output is splitted by columns to account for the procedure at different time points (dates). 

Thank you in advance.

[Tom · Accepted solution]

You need a combination of variables that uniquely identify the observations to use PROC TRANSPOSE.  You could always just run an extra data step to create one first.

data DB;
  input (ID Start End TypeTransfusion1-TypeTransfusion3) (:$20.); 
cards;
0001 2024-09-01 2024-10-01  RBC  Whole blood     .    
0001 2024-09-01 2024-10-01   .        .       Platelet
0002 2016-NK-NK 2016-NK-NK   .    Whole blood Platelet
0002 2016-05-02 2016-05-02  RBC Whole blood      .      
0002 2016-05-05 2016-05-04   .        .       Platelet
0003    .            .       .        .          .    
0003    .            .       .        .          .   
0004 2024-09-01 2024-10-01 RBC Whole blood       .   
0004 2024-09-01 2024-10-01  .         .       Platelet
0005 2025-11-03 2025-11-23  .   Whole blood      .     
0005 2025-11-03 2025-11-14 RBC       .           .  
; 

data for_transpose;
  row+1;
  set db;
run;

proc transpose data=for_transpose out=want(rename=(col1=TypeTransfusion));
  by row id start end;
  var TypeTransfusion1-TypeTransfusion3;
run;

Or just don't bother with using PROC TRANSPOSE and instead just write the data step to transpose the data yourself.

data want2;
  set db;
  length TypeTransfusion $20;
  array list TypeTransfusion1-TypeTransfusion3 ;
  do index=1 to dim(list);
    TypeTransfusion=list[index];
    output;
  end;
  drop TypeTransfusion1-TypeTransfusion3;
run;

You might want to add logic to eliminate the empty values.