Resshape wide problem - data is diagonal

[Jenna Grabey]

I have the following example data but I am trying to put this into wide format so that each STUDY_ID has just one row.

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 STUDY_ID str11 AERPTDAT str8 AERPTIME str26 AETERM float(AESTDAT AEOUT) str1 AESEV float AEENDDAT str2 AEINIT float(AESPID AESER)
"A" "22 Jul 2022" "10:05:03" "Diarrhoea"   22638 1 "2" 22640 "JG" 401 0
"A" "22 Jul 2022" "10:05:03" "Headache"    22639 1 "2" 22640 "JG" 402 0
"B" "22 Jul 2022" "10:05:03" "Headache"    22719 1 "3" 22720 "JG" 403 0
"B" "22 Jul 2022" "10:05:03" "Headache"    22721 5 "3"     . "JG" 404 0
"C" "22 Jul 2022" "10:05:03" "Fever"       22600 1 "2" 22605 "JG" 405 0
"C" "22 Jul 2022" "10:05:03" "Fever"       22606 5 "2"     . "JG" 406 0
"C" "22 Jul 2022" "10:05:03" "Headache"    22600 1 "3" 22605 "JG" 407 0
"C" "22 Jul 2022" "10:05:03" "Headache"    22606 5 "3"     . "JG" 408 0
"C" "22 Jul 2022" "10:05:03" "Muscle ache" 22600 1 "3" 22605 "JG" 409 0
"C" "22 Jul 2022" "10:05:03" "Muscle ache" 22606 5 "3"     . "JG" 410 0
"D" "22 Jul 2022" "10:05:03" "Cough"       22770 1 "2" 22771 "JG" 411 0
"D" "22 Jul 2022" "10:05:03" "Cough"       22772 1 "2" 22773 "JG" 412 0
"D" "22 Jul 2022" "10:05:03" "Cough"       22774 5 "2"     . "JG" 413 0
"E" "22 Jul 2022" "10:05:03" "Cough"       22774 5 "1"     . "JG" 414 0
"E" "22 Jul 2022" "10:05:03" "Headache"    22774 5 "2"     . "JG" 415 0
"F" "22 Jul 2022" "10:05:03" "Cough"       22591 1 "2" 22594 "JG" 416 0
"F" "22 Jul 2022" "10:05:03" "Cough"       22596 1 "2" 22603 "JG" 417 0
"F" "22 Jul 2022" "10:05:03" "Cough"       22615 5 "2"     . "JG" 418 0
"F" "22 Jul 2022" "10:05:03" "Muscle ache" 22598 1 "2" 22599 "JG" 419 0
"F" "22 Jul 2022" "10:05:03" "Muscle ache" 22611 1 "2" 22612 "JG" 420 0
"F" "22 Jul 2022" "10:05:03" "Muscle ache" 22613 1 "2" 22614 "JG" 421 0
"G" "22 Jul 2022" "10:05:03" "Cough"       22610 1 "2" 22620 "JG" 422 0
"G" "22 Jul 2022" "10:05:03" "Cough"       22621 5 "2"     . "JG" 423 0
"G" "22 Jul 2022" "10:05:03" "Diarrhoea"   22602 1 "2" 22606 "JG" 424 0
"G" "22 Jul 2022" "10:05:03" "Diarrhoea"   22610 1 "2" 22620 "JG" 425 0
"G" "22 Jul 2022" "10:05:03" "Diarrhoea"   22621 5 "2"     . "JG" 426 0
"G" "22 Jul 2022" "10:05:03" "Fever"       22610 1 "1" 22620 "JG" 427 0
"G" "22 Jul 2022" "10:05:03" "Fever"       22621 5 "1"     . "JG" 428 0
"G" "22 Jul 2022" "10:05:03" "Headache"    22604 1 "2" 22605 "JG" 429 0
"G" "22 Jul 2022" "10:05:03" "Headache"    22609 1 "2" 22620 "JG" 430 0
"G" "22 Jul 2022" "10:05:03" "Headache"    22621 5 "2"     . "JG" 431 0
"G" "22 Jul 2022" "10:05:03" "Muscle ache" 22610 1 "1" 22620 "JG" 432 0
"G" "22 Jul 2022" "10:05:03" "Muscle ache" 22621 5 "1"     . "JG" 433 0
"H" "22 Jul 2022" "10:05:03" "Headache"    22753 1 "3" 22754 "JG" 434 0
"H" "22 Jul 2022" "10:05:03" "Headache"    22774 5 "3"     . "JG" 435 0
"H" "22 Jul 2022" "10:05:03" "Muscle ache" 22747 1 "3" 22748 "JG" 436 0
"H" "22 Jul 2022" "10:05:03" "Muscle ache" 22774 5 "3"     . "JG" 437 0
"I" "22 Jul 2022" "10:05:03" "Cough"       22774 5 "2"     . "JG" 438 0
"I" "22 Jul 2022" "10:05:03" "Fever"       22774 5 "1"     . "JG" 439 0
"I" "22 Jul 2022" "10:05:03" "Headache"    22758 1 "3" 22759 "JG" 440 0
"I" "22 Jul 2022" "10:05:03" "Headache"    22774 5 "3"     . "JG" 441 0
"J" "22 Jul 2022" "10:05:03" "Cough"       22616 1 "2" 22620 "JG" 442 0
"J" "22 Jul 2022" "10:05:03" "Cough"       22621 1 "2" 22625 "JG" 443 0
"J" "22 Jul 2022" "10:05:03" "Cough"       22628 1 "2" 22630 "JG" 444 0
"K" "22 Jul 2022" "10:05:03" "Headache"    22510 1 "2" 22513 "JG" 445 0
"K" "22 Jul 2022" "10:05:03" "Headache"    22515 1 "2" 22516 "JG" 446 0
"K" "22 Jul 2022" "10:05:03" "Headache"    22517 1 "2" 22518 "JG" 447 0
"K" "22 Jul 2022" "10:05:03" "Headache"    22535 1 "2" 22536 "JG" 448 0
"K" "22 Jul 2022" "10:05:03" "Muscle ache" 22510 1 "3" 22511 "JG" 449 0
"K" "22 Jul 2022" "10:05:03" "Muscle ache" 22529 1 "3" 22531 "JG" 450 0
"K" "22 Jul 2022" "10:05:03" "Muscle ache" 22533 1 "3" 22534 "JG" 451 0
"L" "22 Jul 2022" "10:05:03" "Cough"       22608 1 "2" 22609 "JG" 452 0
"L" "22 Jul 2022" "10:05:03" "Cough"       22610 1 "2" 22611 "JG" 453 0
"L" "22 Jul 2022" "10:05:03" "Cough"       22634 5 "2"     . "JG" 454 0
"L" "22 Jul 2022" "10:05:03" "Diarrhoea"   22614 1 "2" 22615 "JG" 455 0
"L" "22 Jul 2022" "10:05:03" "Diarrhoea"   22634 5 "2"     . "JG" 456 0
"L" "22 Jul 2022" "10:05:03" "Headache"    22607 1 "2" 22608 "JG" 457 0
"L" "22 Jul 2022" "10:05:03" "Headache"    22612 1 "2" 22614 "JG" 458 0
"L" "22 Jul 2022" "10:05:03" "Headache"    22634 5 "2"     . "JG" 459 0
"L" "22 Jul 2022" "10:05:03" "Muscle ache" 22607 1 "2" 22609 "JG" 460 0
"L" "22 Jul 2022" "10:05:03" "Muscle ache" 22634 5 "2"     . "JG" 461 0
"M" "22 Jul 2022" "10:05:03" "Cough"       22624 1 "2" 22633 "JG" 462 0
"M" "22 Jul 2022" "10:05:03" "Cough"       22635 1 "2" 22639 "JG" 463 0
"M" "22 Jul 2022" "10:05:03" "Cough"       22650 5 "2"     . "JG" 464 0
"M" "22 Jul 2022" "10:05:03" "Fever"       22650 5 "2"     . "JG" 465 0
"M" "22 Jul 2022" "10:05:03" "Headache"    22650 5 "2"     . "JG" 466 0
"M" "22 Jul 2022" "10:05:03" "Muscle ache" 22650 5 "2"     . "JG" 467 0
"N" "22 Jul 2022" "10:05:03" "Cough"       22714 1 "2" 22716 "JG" 468 0
"N" "22 Jul 2022" "10:05:03" "Cough"       22715 1 "2" 22716 "JG" 469 0
"O" "22 Jul 2022" "10:05:03" "Cough"       22719 1 "3" 22741 "JG" 470 0
"O" "22 Jul 2022" "10:05:03" "Fever"       22719 1 "3" 22721 "JG" 471 0
"O" "22 Jul 2022" "10:05:03" "Headache"    22720 1 "2" 22721 "JG" 472 0
"O" "22 Jul 2022" "10:05:03" "Fever"       22774 5 "3"     . "JG" 473 0
"P" "22 Jul 2022" "10:05:03" "Headache"    22774 5 "2"     . "JG" 474 0
"P" "22 Jul 2022" "10:05:03" "Muscle ache" 22773 5 "2"     . "JG" 475 0
"P" "22 Jul 2022" "10:05:03" "Diarrhoea"   22489 1 "2" 22491 "JG" 476 0
"Q" "22 Jul 2022" "10:05:03" "Cough"       22502 1 "2" 22503 "JG" 477 0
"Q" "22 Jul 2022" "10:05:03" "Cough"       22504 1 "2" 22505 "JG" 478 0
"Q" "22 Jul 2022" "10:05:03" "Diarrhoea"   22616 1 "3" 22617 "JG" 479 0
"R" "22 Jul 2022" "10:05:03" "Diarrhoea"   22630 1 "2" 22632 "JG" 480 0
"R" "22 Jul 2022" "10:05:03" "Diarrhoea"   22647 1 "2" 22657 "JG" 481 0
"R" "22 Jul 2022" "10:05:03" "Headache"    22630 1 "1" 22632 "JG" 482 0
"R" "22 Jul 2022" "10:05:03" "Headache"    22647 1 "1" 22657 "JG" 483 0
"R" "22 Jul 2022" "10:05:03" "Muscle ache" 22630 1 "1" 22632 "JG" 484 0
"R" "22 Jul 2022" "10:05:03" "Muscle ache" 22647 1 "1" 22657 "JG" 485 0
"R" "22 Jul 2022" "10:05:03" "Headache"    22635 1 "3" 22636 "JG" 486 0
"R" "22 Jul 2022" "10:05:03" "Headache"    22651 1 "2" 22652 "JG" 487 0
"R" "22 Jul 2022" "10:05:03" "Muscle ache" 22716 1 "2" 22717 "JG" 488 0
"S" "22 Jul 2022" "10:05:03" "Cough"       22720 1 "2" 22722 "JG" 489 0
"S" "22 Jul 2022" "10:05:03" "Headache"    22720 1 "2" 22721 "JG" 490 0
"T" "22 Jul 2022" "10:05:03" "Cough"       22732 5 "2"     . "JG" 491 0
"T" "22 Jul 2022" "10:05:03" "Diarrhoea"   22722 1 "2" 22723 "JG" 492 0
"T" "22 Jul 2022" "10:05:03" "Diarrhoea"   22730 1 "2" 22731 "JG" 493 0
"T" "22 Jul 2022" "10:05:03" "Diarrhoea"   22732 5 "2"     . "JG" 494 0
"T" "22 Jul 2022" "10:05:03" "Headache"    22732 5 "2"     . "JG" 495 0
"T" "22 Jul 2022" "10:05:03" "Muscle ache" 22732 5 "2"     . "JG" 496 0
"T" "22 Jul 2022" "10:05:03" "Cough"       22769 1 "3" 22770 "JG" 497 0
"T" "22 Jul 2022" "10:05:03" "Cough"       22774 5 "3"     . "JG" 498 0
"T" "22 Jul 2022" "10:05:03" "Diarrhoea"   22774 5 "2"     . "JG" 499 0
"T" "22 Jul 2022" "10:05:03" "Fever"       22774 5 " "     . "JG" 500 0
end
format %td AESTDAT
format %td AEENDDAT

The code I used is the following:

gen ID2 = _n        
reshape wide AETERM AESTDAT AEOUT AESEV AEENDDAT AESPID, i(STUDY_ID) j(ID2)

But this gave me the following output, which is not the desired result:

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 STUDY_ID str26 AETERM1 float(AESTDAT1 AEOUT1) str1 AESEV1 float(AEENDDAT1 AESPID1) str26 AETERM2 float(AESTDAT2 AEOUT2) str1 AESEV2 float(AEENDDAT2 AESPID2) str26 AETERM3 float(AESTDAT3 AEOUT3) str1 AESEV3 float(AEENDDAT3 AESPID3)
"A" "Diarrhoea" 22638 1 "2" 22640 401 "Headache" 22639 1 "2" 22640 402 ""             . . ""      .   .
"B" ""              . . ""      .   . ""             . . ""      .   . "Headache" 22719 1 "3" 22720 403
"C" ""              . . ""      .   . ""             . . ""      .   . ""             . . ""      .   .
"D" ""              . . ""      .   . ""             . . ""      .   . ""             . . ""      .   .
end
format %td AESTDAT1
format %td AEENDDAT1
format %td AESTDAT2
format %td AEENDDAT2
format %td AESTDAT3
format %td AEENDDAT3

Any help would be much appreciated.

[Nick Cox]

bysort STUDY_ID : gen ID2 = _n

may what you seek, but I doubt that the wide result will make your Stata life easier.

It seems puzzling that numerous patients are recorded with same date and time, but perhaps the example is not realistic in all respects.

[Jenna Grabey]

Thank you Nick, that has solved my issue. As for the same report date and time that is just the date and time the report has been run, the actual start date of the event is the AESTDAT variable, which is the clinically signigicant variable the AERPTDAT and AERPTIME are there for administration purposes.