topic Re: How to transpose multiple variables by two variables? in SAS Programming

How to transpose multiple variables by two variables?

abhi309 — Wed, 01 Mar 2023 22:02:42 GMT

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

Re: How to transpose multiple variables by two variables?

Reeza — Wed, 01 Mar 2023 22:29:11 GMT

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

Re: How to transpose multiple variables by two variables?

ballardw — Wed, 01 Mar 2023 23:36:03 GMT

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.

Re: How to transpose multiple variables by two variables?

abhi309 — Thu, 02 Mar 2023 01:01:21 GMT

Thank you so much that worked.