topic How to convert values on diagonal to long format? in SAS Programming

How to convert values on diagonal to long format?

SAS-questioner — Tue, 09 Apr 2024 20:09:32 GMT

I have a data format like below:

id    time1 time2  time3   
1       1                      
              2                
                    3          
2       1                      
              2                
                    3

The time was on diagonal for each people, but I want to convert it to a long format with one time variable and different times under it:

Does anyone know how to do it? Thank you!

Re: How to convert values on diagonal to long format?

ballardw — Tue, 09 Apr 2024 20:15:55 GMT

I would try:

data want;
   set have;
   time = max(time1,time2,time3);
   drop time1-time3;
run;

Re: How to convert values on diagonal to long format?

mkeintz — Wed, 10 Apr 2024 01:59:55 GMT

Do your second and third rows really have blank ID values?

Re: How to convert values on diagonal to long format?

Ksharp — Wed, 10 Apr 2024 02:07:09 GMT

data have;
input id    time1 time2  time3  ;
cards; 
1       1       .       .        
.        .      2      .          
.        .      .      3          
2       1        .     .         
.        .      2       .         
.        .       .     3 
;
data want;
 set have(rename=(id=_id));
 retain id .;
 if not missing(_id) then id=_id;
 time=coalesce(of time1-time3);
 keep id time;
run;