Generate a date variable from another date variable with an if condition

[Jenna Grabey]

Here I am trying to create a start date (AESTDAT) and end date (AEENDAT) variables from the list below. To try and do this for the start date I tried the following on the sample dataset below:

gen AESTDAT = Diarydate if event=="Start"

However I received the error message: type mismatch. Diarydate - float %td and event - byte %9.0g. So I converted both to strings using the following and then tried:

tostring event, generate (event2)
tostring Diarydate, generate (Diarydate2)
gen AESTDAT= Diarydate2 if event2==1

I still received the same error message, even though both variables were now string variables. Any idea what I am missing here? Or am I approaching this the wrong way entirely?

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 STUDY_ID int AERPTDAT double AERPTIME str18 AETERM int Diarydate str5 event byte AESEV
"A" 22834 -1893385652000 "Increased appetite" 22638 "Start" 2
"A" 22834 -1893385652000 "Increased appetite" 22640 "End"   2
"A" 22834 -1893385652000 "Headache"           22639 "Start" 2
"A" 22834 -1893385652000 "Headache"           22640 "End"   2
"A" 22834 -1893385652000 "Decreased appetite" 22639 "Start" 2
"A" 22834 -1893385652000 "Decreased appetite" 22641 "End"   2
"A" 22834 -1893385652000 "Abdominal pain"     22639 "Start" 2
"A" 22834 -1893385652000 "Abdominal pain"     22640 "End"   2
"A" 22834 -1893385652000 "Abdominal pain"     22643 "Start" 2
"A" 22834 -1893385652000 "Abdominal pain"     22644 "End"   2
"B" 22834 -1893385652000 "Headache"           22719 "Start" 3
"B" 22834 -1893385652000 "Headache"           22720 "End"   3
"B" 22834 -1893385652000 "Headache"           22721 "Start" 3
"C" 22834 -1893385652000 "High BP"            22600 "Start" 2
"C" 22834 -1893385652000 "High BP"            22605 "End"   2
end
format %tddd-Mon-YY AERPTDAT
format %tcHH:MM:SS AERPTIME
format %tddd-Mon-YY Diarydate

Many thanks,
Jenna

[William Lisowski]

Your original code worked for me on your sample data.

. gen AESTDAT = Diarydate if event=="Start"
(7 missing values generated)

I find it odd that your dataex example shows event as a string variable, not a byte with a value label. Have you perhaps forgotten that you encoded it in some work not shown preceding the code that failed?

After you created event2 as a string, you then compared it to a non-string - to the number 1 rather than to the string "1". But don't go down that path. Go back to your original code and fix it: either don't encode your data, or compare the encoded variable to the numeric value assigned to "Start".

[Jenna Grabey]

HI William Lisowski I created the dummy data outside of Stata before using dataex on it. So, when I run my code the Diarydate variable is a float %td and the event variable is a byte %8.0g, but I cannot get that line of code to work still. Thank you for your advice on encoding.

[Jenna Grabey]

I changed the Diarydate variable from a float to an integer and event from byte to string to match the dataex example but that still did not work. So I am really stumped with this. Any help would be much appreciated.

[Jared Greathouse]

Don't make any variables outside of stata. Remove whatever dummy variable you made, and then show us the dataex again

[Jenna Grabey]

Here is my code taken straight from Stata, all I have changed are the IDs and the text for some symtpoms for confidentiality reasons.

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 STUDY_ID str11 AERPTDAT str8 AERPTIME str34 AETERM float Diarydate byte event str1 AESEV str2 AEINIT float AESPID
"A" "11 Jul 2022" "12:18:22" "High BP"                          22638 1 "2" "JG"  402
"A" "11 Jul 2022" "12:18:22" "High BP"                          22640 2 "2" "JG"  404
"A" "11 Jul 2022" "12:18:22" "Headache"                           22639 1 "2" "JG"  431
"A" "11 Jul 2022" "12:18:22" "Headache"                           22640 2 "2" "JG"  432
"A" "11 Jul 2022" "12:18:22" "Generally feeling unwell"           22639 1 "2" "JG"  459
"A" "11 Jul 2022" "12:18:22" "Generally feeling unwell"           22641 2 "2" "JG"  461
"A" "11 Jul 2022" "12:18:22" "Abdominal pain"                     22639 1 "2" "JG"  487
"A" "11 Jul 2022" "12:18:22" "Abdominal pain"                     22640 2 "2" "JG"  488
"A" "11 Jul 2022" "12:18:22" "Abdominal pain"                     22643 1 "2" "JG"  491
"A" "11 Jul 2022" "12:18:22" "Abdominal pain"                     22644 2 "2" "JG"  492
"B" "11 Jul 2022" "12:18:22" "Headache"                           22719 1 "3" "JG"  569
"B" "11 Jul 2022" "12:18:22" "Headache"                           22720 2 "3" "JG"  570
"B" "11 Jul 2022" "12:18:22" "Headache"                           22721 1 "3" "JG"  571
"C" "11 Jul 2022" "12:18:22" "Itchy skin"                              22600 1 "2" "JG"  799
"C" "11 Jul 2022" "12:18:22" "Itchy skin"                              22605 2 "2" "JG"  804
"C" "11 Jul 2022" "12:18:22" "Itchy skin"                              22606 1 "2" "JG"  805
"C" "11 Jul 2022" "12:18:22" "Headache"                           22600 1 "3" "JG"  827
"C" "11 Jul 2022" "12:18:22" "Headache"                           22605 2 "3" "JG"  832
"C" "11 Jul 2022" "12:18:22" "Headache"                           22606 1 "3" "JG"  833
"C" "11 Jul 2022" "12:18:22" "Loss of appetite"                        22600 1 "3" "JG"  883
end
format %td Diarydate
label values event event
label def event 1 "Start", modify
label def event 2 "End", modify

[William Lisowski]

Thank you. Providing example data in post #1 that in fact did not accurately reflect the data you were using is why we are now at post #7 three days later. The disguised data in post #6 was exactly what is required for example data, and the best way of generating example data that does not violate confidentiality restrictions.

Observe that the variable event takes on the numeric values 1 and 2, and that the variable event has been assigned a value label of the same name, so that the value 1 is displayed as "Start" and the value 2 as "End" by commands like list and tab.

Reflect on the output of

tab event
tab event, nolabel
codebook event

Thus from post #1

gen AESTDAT = Diarydate if event=="Start"

should in fact be

gen AESTDAT = Diarydate if event==1

It seems to me that you are unfamiliar with the concept of value labels. You should read Chapter 12, especially sections 12.6 and 12.6, of the Stata User's Guide PDF included in your Stata installation and accessible through Stata's Help menu, to better familiarize yourself with the differences between how data is stored in Stata and how it is presented in the output of Stata commands.

This suggests to me that you may have other gaps in your knowledge of Stata basics. When I began using Stata in a serious way, I started - as others here did - by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through Stata's Help menu. The objective in doing this was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and manual.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

[Jenna Grabey]

Thank you William for your advice, that has helped. I'm sorry that my first example did not help, I am new to using dataex. I will do the reading that you have suggested.

[Jenna Grabey]

An example of my data now is the following. Ideally I need to get the start date (AESTDAT) and the end date (AEENDDAT) of the clinical event onto the same row, and then remove the row from below. So in the example below there would ideally be 3 rows and not 6 rows. If I reshape wide the data it then ends up repeating the clinical event name and other variables which I do not want. What would be the best way to do this?

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 STUDY_ID str11 AERPTDAT str8 AERPTIME str34 AETERM float(AESTDAT AEENDDAT) str1 AESEV str2 AEINIT float AESPID
"A" "11 Jul 2022" "16:08:47" "High BP"                          22638     . "2" "JG"  402
"A" "11 Jul 2022" "16:08:47" "High BP"                              . 22640 "2" "JG"  404
"A" "11 Jul 2022" "16:08:47" "Headache"                           22639     . "2" "JG"  431
"A" "11 Jul 2022" "16:08:47" "Headache"                               . 22640 "2" "JG"  432
"A" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22639     . "2" "JG"  459
"A" "11 Jul 2022" "16:08:47" "Generally feeling unwell"               . 22641 "2" "JG"  461

end
format %td AESTDAT
format %td AEENDDAT

Many thanks,
Jenna

[William Lisowski]

I notice in your data in post #6 there are several instances of Start that are not followed by a corresponding End.

Are these observations sorted in such a way that a Start not immediately followed by an End will not correspond to some End further down in the data? If so, what variables were given in the sort command that resulted in the data in post #6?

Or is it possible that there is an End further down in the data that needs to be matched with the unaccompanied Start? If so, what variables when taken in combination identify pairs that are to be combined?

[William Lisowski]

If I reshape wide the data it then ends up repeating the clinical event name and other variables which I do not want.

Not so. Perhaps this example using your data from post #6 starts you in a useful direction. I took a guess at a likely answer to the questions posed in post #10.

generate pair = sum(event==1)
reshape wide Diarydate AESPID, i(pair STUDY_ID AERPTDAT AERPTIME AETERM) j(event)
describe
list pair STUDY_ID AETERM Diarydate1 Diarydate2, clean noobs abbreviate(20)

. generate pair = sum(event==1)

. reshape wide Diarydate AESPID, i(pair STUDY_ID AERPTDAT AERPTIME AETERM) j(event)
(j = 1 2)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations               20   ->   12          
Number of variables                  10   ->   11          
j variable (2 values)             event   ->   (dropped)
xij variables:
                              Diarydate   ->   Diarydate1 Diarydate2
                                 AESPID   ->   AESPID1 AESPID2
-----------------------------------------------------------------------------

. describe

Contains data
 Observations:            12                  
    Variables:            11                  
------------------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
------------------------------------------------------------------------------------------------
STUDY_ID        str8    %9s                   
AERPTDAT        str11   %11s                  
AERPTIME        str8    %9s                   
AETERM          str34   %34s                  
pair            float   %9.0g                 
Diarydate1      float   %td                   1 Diarydate
AESPID1         float   %9.0g                 1 AESPID
Diarydate2      float   %td                   2 Diarydate
AESPID2         float   %9.0g                 2 AESPID
AESEV           str1    %9s                   
AEINIT          str2    %9s                   
------------------------------------------------------------------------------------------------
Sorted by: pair  STUDY_ID  AERPTDAT  AERPTIME  AETERM
     Note: Dataset has changed since last saved.

. list pair STUDY_ID AETERM Diarydate1 Diarydate2, clean noobs abbreviate(20)

    pair   STUDY_ID                     AETERM   Diarydate1   Diarydate2  
       1          A                    High BP    24dec2021    26dec2021  
       2          A                   Headache    25dec2021    26dec2021  
       3          A   Generally feeling unwell    25dec2021    27dec2021  
       4          A             Abdominal pain    25dec2021    26dec2021  
       5          A             Abdominal pain    29dec2021    30dec2021  
       6          B                   Headache    15mar2022    16mar2022  
       7          B                   Headache    17mar2022            .  
       8          C                 Itchy skin    16nov2021    21nov2021  
       9          C                 Itchy skin    22nov2021            .  
      10          C                   Headache    16nov2021    21nov2021  
      11          C                   Headache    22nov2021            .  
      12          C           Loss of appetite    16nov2021            .  

.

[Jenna Grabey]

I notice in your data in post #6 there are several instances of Start that are not followed by a corresponding End.

Are these observations sorted in such a way that a Start not immediately followed by an End will not correspond to some End further down in the data? If so, what variables were given in the sort command that resulted in the data in post #6?

Or is it possible that there is an End further down in the data that needs to be matched with the unaccompanied Start? If so, what variables when taken in combination identify pairs that are to be combined?

This is the whole dataex output I didn't want to provide too much data or make it cumbersome for the forum.

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 STUDY_ID str11 AERPTDAT str8 AERPTIME str34 AETERM float(AESTDAT AEENDDAT) str1 AESEV str2 AEINIT float AESPID
"A" "11 Jul 2022" "16:08:47" "Diarrhoea"                          22638     . "2" "JG"  402
"A" "11 Jul 2022" "16:08:47" "Diarrhoea"                              . 22640 "2" "JG"  404
"A" "11 Jul 2022" "16:08:47" "Headache"                           22639     . "2" "JG"  431
"A" "11 Jul 2022" "16:08:47" "Headache"                               . 22640 "2" "JG"  432
"A" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22639     . "2" "JG"  459
"A" "11 Jul 2022" "16:08:47" "Generally feeling unwell"               . 22641 "2" "JG"  461
"A" "11 Jul 2022" "16:08:47" "Abdominal pain"                     22639     . "2" "JG"  487
"A" "11 Jul 2022" "16:08:47" "Abdominal pain"                         . 22640 "2" "JG"  488
"A" "11 Jul 2022" "16:08:47" "Abdominal pain"                     22643     . "2" "JG"  491
"A" "11 Jul 2022" "16:08:47" "Abdominal pain"                         . 22644 "2" "JG"  492
"B" "11 Jul 2022" "16:08:47" "Headache"                           22719     . "3" "JG"  569
"B" "11 Jul 2022" "16:08:47" "Headache"                               . 22720 "3" "JG"  570
"B" "11 Jul 2022" "16:08:47" "Headache"                           22721     . "3" "JG"  571
"C" "11 Jul 2022" "16:08:47" "Fever"                              22600     . "2" "JG"  799
"C" "11 Jul 2022" "16:08:47" "Fever"                                  . 22605 "2" "JG"  804
"C" "11 Jul 2022" "16:08:47" "Fever"                              22606     . "2" "JG"  805
"C" "11 Jul 2022" "16:08:47" "Headache"                           22600     . "3" "JG"  827
"C" "11 Jul 2022" "16:08:47" "Headache"                               . 22605 "3" "JG"  832
"C" "11 Jul 2022" "16:08:47" "Headache"                           22606     . "3" "JG"  833
"C" "11 Jul 2022" "16:08:47" "Muscle ache"                        22600     . "3" "JG"  883
"C" "11 Jul 2022" "16:08:47" "Muscle ache"                            . 22605 "3" "JG"  888
"C" "11 Jul 2022" "16:08:47" "Muscle ache"                        22606     . "3" "JG"  889
"C" "11 Jul 2022" "16:08:47" "Nausea vomiting"                    22594     . "2" "JG"  905
"C" "11 Jul 2022" "16:08:47" "Nausea vomiting"                        . 22596 "2" "JG"  907
"C" "11 Jul 2022" "16:08:47" "Nausea vomiting"                    22597     . "2" "JG"  908
"C" "11 Jul 2022" "16:08:47" "Nausea vomiting"                        . 22598 "2" "JG"  909
"C" "11 Jul 2022" "16:08:47" "Nausea vomiting"                    22600     . "2" "JG"  911
"C" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22600     . "3" "JG"  939
"C" "11 Jul 2022" "16:08:47" "Generally feeling unwell"               . 22605 "3" "JG"  944
"C" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22606     . "3" "JG"  945
"D" "11 Jul 2022" "16:08:47" "Cough"                              22770     . "2" "JG" 1045
"D" "11 Jul 2022" "16:08:47" "Cough"                                  . 22771 "2" "JG" 1046
"D" "11 Jul 2022" "16:08:47" "Cough"                              22772     . "2" "JG" 1047
"D" "11 Jul 2022" "16:08:47" "Cough"                                  . 22773 "2" "JG" 1048
"D" "11 Jul 2022" "16:08:47" "Cough"                              22774     . "2" "JG" 1049
"E" "11 Jul 2022" "16:08:47" "Cough"                              22774     . "1" "JG" 1077
"E" "11 Jul 2022" "16:08:47" "Headache"                           22774     . "2" "JG" 1105
"E" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22774     . "2" "JG" 1161
"F" "11 Jul 2022" "16:08:47" "Abdominal pain"                     22510     . "3" "JG" 1353
"F" "11 Jul 2022" "16:08:47" "Abdominal pain"                         . 22511 "3" "JG" 1354
"G" "11 Jul 2022" "16:08:47" "Cough"                              22591     . "2" "JG" 1412
"G" "11 Jul 2022" "16:08:47" "Cough"                                  . 22594 "2" "JG" 1415
"G" "11 Jul 2022" "16:08:47" "Cough"                              22596     . "2" "JG" 1417
"G" "11 Jul 2022" "16:08:47" "Cough"                                  . 22603 "2" "JG" 1424
"G" "11 Jul 2022" "16:08:47" "Cough"                              22615     . "2" "JG" 1436
"G" "11 Jul 2022" "16:08:47" "Muscle ache"                        22598     . "2" "JG" 1475
"G" "11 Jul 2022" "16:08:47" "Muscle ache"                            . 22599 "2" "JG" 1476
"G" "11 Jul 2022" "16:08:47" "Muscle ache"                        22611     . "2" "JG" 1488
"G" "11 Jul 2022" "16:08:47" "Muscle ache"                            . 22612 "2" "JG" 1489
"G" "11 Jul 2022" "16:08:47" "Muscle ache"                        22613     . "2" "JG" 1490
"G" "11 Jul 2022" "16:08:47" "Muscle ache"                            . 22614 "2" "JG" 1491
"G" "11 Jul 2022" "16:08:47" "Shortness of breath"                22588     . "2" "JG" 1493
"G" "11 Jul 2022" "16:08:47" "Shortness of breath"                    . 22589 "2" "JG" 1494
"G" "11 Jul 2022" "16:08:47" "Shortness of breath"                22591     . "2" "JG" 1496
"G" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22596     . "2" "JG" 1529
"G" "11 Jul 2022" "16:08:47" "Generally feeling unwell"               . 22597 "2" "JG" 1530
"G" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22598     . "2" "JG" 1531
"G" "11 Jul 2022" "16:08:47" "Generally feeling unwell"               . 22599 "2" "JG" 1532
"H" "11 Jul 2022" "16:08:47" "Cough"                              22610     . "2" "JG" 1586
"H" "11 Jul 2022" "16:08:47" "Cough"                                  . 22620 "2" "JG" 1596
"H" "11 Jul 2022" "16:08:47" "Cough"                              22621     . "2" "JG" 1597
"H" "11 Jul 2022" "16:08:47" "Diarrhoea"                          22602     . "2" "JG" 1606
"H" "11 Jul 2022" "16:08:47" "Diarrhoea"                              . 22606 "2" "JG" 1610
"H" "11 Jul 2022" "16:08:47" "Diarrhoea"                          22610     . "2" "JG" 1614
"H" "11 Jul 2022" "16:08:47" "Diarrhoea"                              . 22620 "2" "JG" 1624
"H" "11 Jul 2022" "16:08:47" "Diarrhoea"                          22621     . "2" "JG" 1625
"H" "11 Jul 2022" "16:08:47" "Fever"                              22610     . "1" "JG" 1642
"H" "11 Jul 2022" "16:08:47" "Fever"                                  . 22620 "1" "JG" 1652
"H" "11 Jul 2022" "16:08:47" "Fever"                              22621     . "1" "JG" 1653
"H" "11 Jul 2022" "16:08:47" "Headache"                           22604     . "2" "JG" 1664
"H" "11 Jul 2022" "16:08:47" "Headache"                               . 22605 "2" "JG" 1665
"H" "11 Jul 2022" "16:08:47" "Headache"                           22609     . "2" "JG" 1669
"H" "11 Jul 2022" "16:08:47" "Headache"                               . 22620 "2" "JG" 1680
"H" "11 Jul 2022" "16:08:47" "Headache"                           22621     . "2" "JG" 1681
"H" "11 Jul 2022" "16:08:47" "Muscle ache"                        22610     . "1" "JG" 1726
"H" "11 Jul 2022" "16:08:47" "Muscle ache"                            . 22620 "1" "JG" 1736
"H" "11 Jul 2022" "16:08:47" "Muscle ache"                        22621     . "1" "JG" 1737
"H" "11 Jul 2022" "16:08:47" "Nausea vomiting"                    22610     . "1" "JG" 1754
"H" "11 Jul 2022" "16:08:47" "Nausea vomiting"                        . 22620 "1" "JG" 1764
"H" "11 Jul 2022" "16:08:47" "Nausea vomiting"                    22621     . "1" "JG" 1765
"H" "11 Jul 2022" "16:08:47" "Shortness of breath"                22610     . "2" "JG" 1782
"H" "11 Jul 2022" "16:08:47" "Shortness of breath"                    . 22620 "2" "JG" 1792
"H" "11 Jul 2022" "16:08:47" "Shortness of breath"                22621     . "2" "JG" 1793
"H" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22610     . "2" "JG" 1810
"H" "11 Jul 2022" "16:08:47" "Generally feeling unwell"               . 22620 "2" "JG" 1820
"H" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22621     . "2" "JG" 1821
"H" "11 Jul 2022" "16:08:47" "Abdominal pain"                     22610     . "2" "JG" 1838
"H" "11 Jul 2022" "16:08:47" "Abdominal pain"                         . 22620 "2" "JG" 1848
"H" "11 Jul 2022" "16:08:47" "Abdominal pain"                     22621     . "2" "JG" 1849
"H" "11 Jul 2022" "16:08:47" "Symptoms of uniary tract infection" 22610     . " " "JG" 1866
"H" "11 Jul 2022" "16:08:47" "Symptoms of uniary tract infection"     . 22620 " " "JG" 1876
"H" "11 Jul 2022" "16:08:47" "Symptoms of uniary tract infection" 22621     . " " "JG" 1877
"I" "11 Jul 2022" "16:08:47" "Headache"                           22753     . "3" "JG" 2199
"I" "11 Jul 2022" "16:08:47" "Headache"                               . 22754 "3" "JG" 2200
"I" "11 Jul 2022" "16:08:47" "Headache"                           22774     . "3" "JG" 2220
"I" "11 Jul 2022" "16:08:47" "Muscle ache"                        22747     . "3" "JG" 2249
"I" "11 Jul 2022" "16:08:47" "Muscle ache"                            . 22748 "3" "JG" 2250
"I" "11 Jul 2022" "16:08:47" "Muscle ache"                        22774     . "3" "JG" 2276
"I" "11 Jul 2022" "16:08:47" "Shortness of breath"                22774     . "1" "JG" 2304
"I" "11 Jul 2022" "16:08:47" "Generally feeling unwell"           22774     . "2" "JG" 2332
end
format %td AESTDAT
format %td AEENDDAT

[Jenna Grabey]

I now seem to have this all sorted. Thank you William Lisowski for your advice. The following is the code I used, which then gave me the following output.

*RESHAPE  
generate pair = sum(event==1)
reshape wide Diarydate AESTDAT AEENDDAT AEINIT AESPID, i(pair STUDY_ID AERPTDAT AERPTIME AETERM AESEV) j(event)
drop pair
drop AEENDDAT1
drop Diarydate1
drop AESTDAT2
drop Diarydate2
drop AEINIT2
drop AESPID1 AESPID2
gen AESPID = _n + 400
rename AESTDAT1 AESTDAT
rename AEENDDAT2 AEENDDAT
rename AEINIT1 AEINIT

*AEOUT - AE outcome
*1 = Resolved
*5 = Unknown
gen AEOUT = 1 if AEENDDAT ~=.
quietly replace AEOUT= 5 if AEENDDAT==.
order AESTDAT, after (AETERM)
order AEOUT, after (AESTDAT)
order AEENDDAT, after (AESEV)

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 STUDY_ID str11 AERPTDAT str8 AERPTIME str34 AETERM float(AESTDAT AEOUT) str1 AESEV float AEENDDAT str2 AEINIT float AESPID
"A" "12 Jul 2022" "12:01:02" "Diarrhoea"                          22638 1 "2" 22640 "JG" 401
"A" "12 Jul 2022" "12:01:02" "Headache"                           22639 1 "2" 22640 "JG" 402
"A" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22639 1 "2" 22641 "JG" 403
"A" "12 Jul 2022" "12:01:02" "Abdominal pain"                     22639 1 "2" 22640 "JG" 404
"A" "12 Jul 2022" "12:01:02" "Abdominal pain"                     22643 1 "2" 22644 "JG" 405
"B" "12 Jul 2022" "12:01:02" "Headache"                           22719 1 "3" 22720 "JG" 406
"B" "12 Jul 2022" "12:01:02" "Headache"                           22721 . "3"     . "JG" 407
"C" "12 Jul 2022" "12:01:02" "Fever"                              22600 1 "2" 22605 "JG" 408
"C" "12 Jul 2022" "12:01:02" "Fever"                              22606 . "2"     . "JG" 409
"C" "12 Jul 2022" "12:01:02" "Headache"                           22600 1 "3" 22605 "JG" 410
"C" "12 Jul 2022" "12:01:02" "Headache"                           22606 . "3"     . "JG" 411
"C" "12 Jul 2022" "12:01:02" "Muscle ache"                        22600 1 "3" 22605 "JG" 412
"C" "12 Jul 2022" "12:01:02" "Muscle ache"                        22606 . "3"     . "JG" 413
"C" "12 Jul 2022" "12:01:02" "Nausea vomiting"                    22594 1 "2" 22596 "JG" 414
"C" "12 Jul 2022" "12:01:02" "Nausea vomiting"                    22597 1 "2" 22598 "JG" 415
"C" "12 Jul 2022" "12:01:02" "Nausea vomiting"                    22600 . "2"     . "JG" 416
"C" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22600 1 "3" 22605 "JG" 417
"C" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22606 . "3"     . "JG" 418
"D" "12 Jul 2022" "12:01:02" "Cough"                              22770 1 "2" 22771 "JG" 419
"D" "12 Jul 2022" "12:01:02" "Cough"                              22772 1 "2" 22773 "JG" 420
"D" "12 Jul 2022" "12:01:02" "Cough"                              22774 . "2"     . "JG" 421
"E" "12 Jul 2022" "12:01:02" "Cough"                              22774 . "1"     . "JG" 422
"E" "12 Jul 2022" "12:01:02" "Headache"                           22774 . "2"     . "JG" 423
"E" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22774 . "2"     . "JG" 424
"F" "12 Jul 2022" "12:01:02" "Abdominal pain"                     22510 1 "3" 22511 "JG" 425
"G" "12 Jul 2022" "12:01:02" "Cough"                              22591 1 "2" 22594 "JG" 426
"G" "12 Jul 2022" "12:01:02" "Cough"                              22596 1 "2" 22603 "JG" 427
"G" "12 Jul 2022" "12:01:02" "Cough"                              22615 . "2"     . "JG" 428
"G" "12 Jul 2022" "12:01:02" "Muscle ache"                        22598 1 "2" 22599 "JG" 429
"G" "12 Jul 2022" "12:01:02" "Muscle ache"                        22611 1 "2" 22612 "JG" 430
"G" "12 Jul 2022" "12:01:02" "Muscle ache"                        22613 1 "2" 22614 "JG" 431
"G" "12 Jul 2022" "12:01:02" "Shortness of breath"                22588 1 "2" 22589 "JG" 432
"G" "12 Jul 2022" "12:01:02" "Shortness of breath"                22591 . "2"     . "JG" 433
"G" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22596 1 "2" 22597 "JG" 434
"G" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22598 1 "2" 22599 "JG" 435
"H" "12 Jul 2022" "12:01:02" "Cough"                              22610 1 "2" 22620 "JG" 436
"H" "12 Jul 2022" "12:01:02" "Cough"                              22621 . "2"     . "JG" 437
"H" "12 Jul 2022" "12:01:02" "Diarrhoea"                          22602 1 "2" 22606 "JG" 438
"H" "12 Jul 2022" "12:01:02" "Diarrhoea"                          22610 1 "2" 22620 "JG" 439
"H" "12 Jul 2022" "12:01:02" "Diarrhoea"                          22621 . "2"     . "JG" 440
"H" "12 Jul 2022" "12:01:02" "Fever"                              22610 1 "1" 22620 "JG" 441
"H" "12 Jul 2022" "12:01:02" "Fever"                              22621 . "1"     . "JG" 442
"H" "12 Jul 2022" "12:01:02" "Headache"                           22604 1 "2" 22605 "JG" 443
"H" "12 Jul 2022" "12:01:02" "Headache"                           22609 1 "2" 22620 "JG" 444
"H" "12 Jul 2022" "12:01:02" "Headache"                           22621 . "2"     . "JG" 445
"H" "12 Jul 2022" "12:01:02" "Muscle ache"                        22610 1 "1" 22620 "JG" 446
"H" "12 Jul 2022" "12:01:02" "Muscle ache"                        22621 . "1"     . "JG" 447
"H" "12 Jul 2022" "12:01:02" "Nausea vomiting"                    22610 1 "1" 22620 "JG" 448
"H" "12 Jul 2022" "12:01:02" "Nausea vomiting"                    22621 . "1"     . "JG" 449
"H" "12 Jul 2022" "12:01:02" "Shortness of breath"                22610 1 "2" 22620 "JG" 450
"H" "12 Jul 2022" "12:01:02" "Shortness of breath"                22621 . "2"     . "JG" 451
"H" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22610 1 "2" 22620 "JG" 452
"H" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22621 . "2"     . "JG" 453
"H" "12 Jul 2022" "12:01:02" "Abdominal pain"                     22610 1 "2" 22620 "JG" 454
"H" "12 Jul 2022" "12:01:02" "Abdominal pain"                     22621 . "2"     . "JG" 455
"H" "12 Jul 2022" "12:01:02" "Symptoms of uniary tract infection" 22610 1 " " 22620 "JG" 456
"H" "12 Jul 2022" "12:01:02" "Symptoms of uniary tract infection" 22621 . " "     . "JG" 457
"I" "12 Jul 2022" "12:01:02" "Headache"                           22753 1 "3" 22754 "JG" 458
"I" "12 Jul 2022" "12:01:02" "Headache"                           22774 . "3"     . "JG" 459
"I" "12 Jul 2022" "12:01:02" "Muscle ache"                        22747 1 "3" 22748 "JG" 460
"I" "12 Jul 2022" "12:01:02" "Muscle ache"                        22774 . "3"     . "JG" 461
"I" "12 Jul 2022" "12:01:02" "Shortness of breath"                22774 . "1"     . "JG" 462
"I" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22774 . "2"     . "JG" 463
"J" "12 Jul 2022" "12:01:02" "Cough"                              22774 . "2"     . "JG" 464
"J" "12 Jul 2022" "12:01:02" "Fever"                              22774 . "1"     . "JG" 465
"J" "12 Jul 2022" "12:01:02" "Headache"                           22758 1 "3" 22759 "JG" 466
"J" "12 Jul 2022" "12:01:02" "Headache"                           22774 . "3"     . "JG" 467
"J" "12 Jul 2022" "12:01:02" "Shortness of breath"                22757 1 "2" 22758 "JG" 468
"J" "12 Jul 2022" "12:01:02" "Shortness of breath"                22774 . "2"     . "JG" 469
"J" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22774 . "3"     . "JG" 470
"K" "12 Jul 2022" "12:01:02" "Shortness of breath"                22399 1 "2" 22400 "JG" 471
"L" "12 Jul 2022" "12:01:02" "Cough"                              22616 1 "2" 22620 "JG" 472
"L" "12 Jul 2022" "12:01:02" "Cough"                              22621 1 "2" 22625 "JG" 473
"L" "12 Jul 2022" "12:01:02" "Cough"                              22628 1 "2" 22630 "JG" 474
"L" "12 Jul 2022" "12:01:02" "Shortness of breath"                22623 1 "2" 22625 "JG" 475
"L" "12 Jul 2022" "12:01:02" "Shortness of breath"                22626 1 "2" 22627 "JG" 476
"M" "12 Jul 2022" "12:01:02" "Headache"                           22510 1 "2" 22513 "JG" 477
"M" "12 Jul 2022" "12:01:02" "Headache"                           22515 1 "2" 22516 "JG" 478
"M" "12 Jul 2022" "12:01:02" "Headache"                           22517 1 "2" 22518 "JG" 479
"M" "12 Jul 2022" "12:01:02" "Headache"                           22535 1 "2" 22536 "JG" 480
"M" "12 Jul 2022" "12:01:02" "Muscle ache"                        22510 1 "3" 22511 "JG" 481
"M" "12 Jul 2022" "12:01:02" "Muscle ache"                        22529 1 "3" 22531 "JG" 482
"M" "12 Jul 2022" "12:01:02" "Muscle ache"                        22533 1 "3" 22534 "JG" 483
"N" "12 Jul 2022" "12:01:02" "Cough"                              22608 1 "2" 22609 "JG" 484
"N" "12 Jul 2022" "12:01:02" "Cough"                              22610 1 "2" 22611 "JG" 485
"N" "12 Jul 2022" "12:01:02" "Cough"                              22634 . "2"     . "JG" 486
"N" "12 Jul 2022" "12:01:02" "Diarrhoea"                          22614 1 "2" 22615 "JG" 487
"N" "12 Jul 2022" "12:01:02" "Diarrhoea"                          22634 . "2"     . "JG" 488
"N" "12 Jul 2022" "12:01:02" "Headache"                           22607 1 "2" 22608 "JG" 489
"N" "12 Jul 2022" "12:01:02" "Headache"                           22612 1 "2" 22614 "JG" 490
"N" "12 Jul 2022" "12:01:02" "Headache"                           22634 . "2"     . "JG" 491
"N" "12 Jul 2022" "12:01:02" "Muscle ache"                        22607 1 "2" 22609 "JG" 492
"N" "12 Jul 2022" "12:01:02" "Muscle ache"                        22634 . "2"     . "JG" 493
"N" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22608 1 "2" 22609 "JG" 494
"N" "12 Jul 2022" "12:01:02" "Generally feeling unwell"           22634 . "2"     . "JG" 495
"O" "12 Jul 2022" "12:01:02" "Cough"                              22624 1 "2" 22633 "JG" 496
"O" "12 Jul 2022" "12:01:02" "Cough"                              22635 1 "2" 22639 "JG" 497
"O" "12 Jul 2022" "12:01:02" "Cough"                              22650 . "2"     . "JG" 498
"O" "12 Jul 2022" "12:01:02" "Fever"                              22650 . "2"     . "JG" 499
"O" "12 Jul 2022" "12:01:02" "Headache"                           22650 . "2"     . "JG" 500
end
format %td AESTDAT
format %td AEENDDAT

[William Lisowski]

I am pleased you were able to solve your problem.

For the benefit of those who, at a later date, might be led to this topic through a search, let me clarify my advice.

Once it became clear that the objective was to reduce each Start/End pair of observations to a single observation, it was no longer necessary to create the AESTDAT and AEENDDAT variables, and in fact, making use of them would complicate the code, as demonstrated in post #13. The effort in post #1 through post #8 was heading in the wrong direction.

In post #11 I used the example data as presented in post #6, which included the Diarydate variable not included in the data in post #12, and did not include the AESTDAT and AEENDDAT variables which were not presented in post #6. I believe post #6 is a representation of the data as it originally existed, before starting down the wrong path (of course with the exception of the editing required to preserve confidentiality). The objective of post #11 in part was to demonstrate how reshape wide works, and that reshape wide does not need to duplicate variables that have the same value in the Start and End observations, as suggested in post #9.

In the output shown in post #11, the variables Diarydate1 and Diarydate2 correspond to the value of Diarydate in the observations for which event==1 ("Start") and event1==2 ("End"), respectively. Adding

rename (Diarydate1 Diarydate2) (AESTDAT AEENDDAT)

might have made the connection more obvious.