Transform a table: making options as variable

[Vini Fardhdiani]

Dear all,

I have this type of table:

ID	Date	Culture ID	Culture type	Sample result
1	1 Jan 2022	001	MGIT	Positive
1	1 Jan 2022	001	LJ	Positive
2	1 Jan 2022	001	MGIT	Negative
2	1 Jan 2022	001	LJ	Positive
2	2 Jan 2022	002	MGIT	Positive

I would like to transform it into:

ID	Date	Culture ID	MGIT	LJ
1	1 Jan 2022	001	Positive	Positive
2	1 Jan 2022	001	Negative	Positive
2	2 Jan 2022	002	Positive

Could you please help me with the syntax that I could use?

Thank you in advance.

[Andrew Musau]

Assumes that culturetype is a string variable.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id str10 date str3 cultureid str4 culturetype str8 sampleresult
1 "1 Jan 2022" "001" "MGIT" "Positive"
1 "1 Jan 2022" "001" "LJ"   "Positive"
2 "1 Jan 2022" "001" "MGIT" "Negative"
2 "1 Jan 2022" "001" "LJ"   "Positive"
2 "2 Jan 2022" "002" "MGIT" "Positive"
end


reshape wide sampleresult, i(id date cultureid) j(culturetype) string
rename sampleresult* *

Res.:

. l

     +--------------------------------------------------+
     | id         date   cultur~d         LJ       MGIT |
     |--------------------------------------------------|
  1. |  1   1 Jan 2022        001   Positive   Positive |
  2. |  2   1 Jan 2022        001   Positive   Negative |
  3. |  2   2 Jan 2022        002              Positive |
     +--------------------------------------------------+

[Vini Fardhdiani]

Thank you for your quick answer, Andrew. It worked perfectly!