How to transpose multiple variables by two variables?

[abhi309]

I have data set which looks like:

ID      DATE                  DIAG1   DIAG2  DIAG3  DIAG4  DIAG5

1        01AUG2021       O10.2    F2.1      I9.1       Z35.0    F12.6

1        01AUG2021       T71.2     I9.1       I8.0       H13.1    L11.0

1        05SEP2021        H9.5      Z32.1    K5.8     C87.1    B9.3

1        05SEP2021        J9.0       B71.9    Z39.0   K41.1    

1        05SEP2021        K12.1    

2        13JUL2021         O7.1     F23.1    G5.7

2        13JUL2021         T91.0    G5.7     L43.1

2        13JUL2021         O7.1

2        13 JUL2021        M1.2     G5.7     T91.3

2         09DEC2021        B3.1       

2         09DEC2021        J23.1    J23.4

I want DIAG1 to DIAG5 for each ID and date as one record. So if one ID have multiple rows for one date, I want them all the DIAG in one row

The output I want;

ID DATE            DIAG1   DIAG2  DIAG3  DIAG4  DIAG5  DIAG6  DIAG7  DIAG8  DIAG9  DIAG10 

1  01AUG2021  O10.2    F2.1      I9.1       Z35.0    F12.6   T71.2   I9.1       I8.0       H13.1    L11.0    1  05SEP2021   H9.5      Z32.1    K5.8     C87.1    B9.3     J9.0     B71.9    Z39.0    K41.1    K12.1

2  13JUL2021    O7.1     F23.1    G5.7     T91.0    G5.7     L43.1   O7.1     M1.2     G5.7     T91.3

2  09DEC2021   B3.1     J23.1    J23.4

Thank you

[Reeza]

I see the date repeated in the output line but not the headers. Can you clarify how you want the output?

[abhi309]

Thank you so much that worked.

[ballardw · Accepted solution]

I think you missed a line feed in your "want" example.

Please provide example data as data step pasted into a text box opened on the forum with </> icon that appears above the message window. The text box is to prevent the forum software from reformatting pasted text resulting in code that won't run.

Or at least paste example data in the text box.

Here's my take on this. You actually are not transposing by "two variables" but combining rows in a more complex manner. Which means it is better to start with new data set with just Id Date and the Value. THEN transpose.

data have;
   input ID DATE :date9. DIAG1 $ DIAG2 $ DIAG3 $ DIAG4 $ DIAG5 $ ;
   format date date9.;
datalines;
1 01AUG2021 O10.2 F2.1 I9.1 Z35.0 F12.6
1 01AUG2021 T71.2 I9.1 I8.0 H13.1 L11.0
1 05SEP2021 H9.5 Z32.1 K5.8 C87.1 B9.3
1 05SEP2021 J9.0 B71.9 Z39.0 K41.1 . 
1 05SEP2021 K12.1 . . . .
2 13JUL2021 O7.1 F23.1 G5.7 . .
2 13JUL2021 T91.0 G5.7 L43.1 . .
2 13JUL2021 O7.1 . . . .
2 13JUL2021 M1.2 G5.7 T91.3 . .
2 09DEC2021 B3.1 . . . .
2 09DEC2021 J23.1 J23.4 . . .
;

data temp;
  set have;
  array d (*) diag: ;
  do i=1 to dim(d);
     if not missing(d[i]) then do;
        Value=d[i];
        output;
     end;
  end;
  keep id date value;
run;

/* actually that TEMP set may be more useful */
proc sort data=temp;
   by id date;
run;

proc transpose data=temp out=want (drop=_name_)
   prefix=Diag;
   by id date;
   var value;
run;
     
   

Depending on exactly what you are going to do next with this data the TEMP data set above that I created may be more flexible.