Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Stacking data

    Hey Y'all,

    I have a database that rather than having one row per patient ID with 18 co-morbidity variables with binary "No" and "Yes" values, it has 18 separate rows for each co-morbidity. Not sure if I need to use the stack command to accomplish this task. I imagine that in order to make each co-morbidity value a variable in the first row I will have to get rid of spaces (eg change Atrial Fibrillation to Atrial_Fibrillation). Will also plan to convert no and yes to 0 and 1 and destring. Any thoughts on how to accomplish this?

    Here's my data.

    Thanks!
    Waseem

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str7 ncdrpatientid str19 arrivaldatetime str31 conditionhx str3 conditionhxoccurence
"6620408" "12/30/2020 05:50:00" "Atrial Fibrillation"             "Yes"
"6620408" "12/30/2020 05:50:00" "Atrial Flutter"                  "No" 
"6620408" "12/30/2020 05:50:00" "Carotid Artery Stenosis"         "No" 
"6620408" "12/30/2020 05:50:00" "Cerebrovascular Accident"        "No" 
"6620408" "12/30/2020 05:50:00" "Cerebrovascular Disease"         "No" 
"6620408" "12/30/2020 05:50:00" "Chronic Lung Disease"            "Yes"
"6620408" "12/30/2020 05:50:00" "Conduction Defect"               "No" 
"6620408" "12/30/2020 05:50:00" "Dementia - Moderate to Severe"   "No" 
"6620408" "12/30/2020 05:50:00" "Diabetes Mellitus"               "Yes"
"6620408" "12/30/2020 05:50:00" "Endocarditis"                    "No" 
"6620408" "12/30/2020 05:50:00" "Heart Failure"                   "Yes"
"6620408" "12/30/2020 05:50:00" "Hostile Chest"                   "No" 
"6620408" "12/30/2020 05:50:00" "Hypertension"                    "Yes"
"6620408" "12/30/2020 05:50:00" "Liver Disease"                   "No" 
"6620408" "12/30/2020 05:50:00" "Myocardial Infarction"           "No" 
"6620408" "12/30/2020 05:50:00" "Peripheral Arterial Disease"     "No" 
"6620408" "12/30/2020 05:50:00" "Porcelain Aorta"                 "No" 
"6620408" "12/30/2020 05:50:00" "Transient Ischemic Attack (TIA)" "No" 
"6547037" "10/27/2020 18:09:00" "Atrial Fibrillation"             "No" 
"6547037" "10/27/2020 18:09:00" "Atrial Flutter"                  "No" 
"6547037" "10/27/2020 18:09:00" "Carotid Artery Stenosis"         "No" 
"6547037" "10/27/2020 18:09:00" "Cerebrovascular Accident"        "No" 
"6547037" "10/27/2020 18:09:00" "Cerebrovascular Disease"         "No" 
"6547037" "10/27/2020 18:09:00" "Chronic Lung Disease"            "No" 
"6547037" "10/27/2020 18:09:00" "Conduction Defect"               "Yes"
"6547037" "10/27/2020 18:09:00" "Dementia - Moderate to Severe"   "No" 
"6547037" "10/27/2020 18:09:00" "Diabetes Mellitus"               "No" 
"6547037" "10/27/2020 18:09:00" "Endocarditis"                    "No" 
"6547037" "10/27/2020 18:09:00" "Heart Failure"                   "Yes"
"6547037" "10/27/2020 18:09:00" "Hostile Chest"                   "No" 
"6547037" "10/27/2020 18:09:00" "Hypertension"                    "No" 
"6547037" "10/27/2020 18:09:00" "Liver Disease"                   "No" 
"6547037" "10/27/2020 18:09:00" "Myocardial Infarction"           "No" 
"6547037" "10/27/2020 18:09:00" "Peripheral Arterial Disease"     "No" 
"6547037" "10/27/2020 18:09:00" "Porcelain Aorta"                 "No" 
"6547037" "10/27/2020 18:09:00" "Transient Ischemic Attack (TIA)" "No" 
"6621129" "01/06/2021 06:10:00" "Atrial Fibrillation"             "Yes"
"6621129" "01/06/2021 06:10:00" "Atrial Flutter"                  "No" 
"6621129" "01/06/2021 06:10:00" "Carotid Artery Stenosis"         "No" 
"6621129" "01/06/2021 06:10:00" "Cerebrovascular Accident"        "No" 
"6621129" "01/06/2021 06:10:00" "Cerebrovascular Disease"         "No" 
"6621129" "01/06/2021 06:10:00" "Chronic Lung Disease"            "No" 
"6621129" "01/06/2021 06:10:00" "Conduction Defect"               "No" 
"6621129" "01/06/2021 06:10:00" "Dementia - Moderate to Severe"   "No" 
"6621129" "01/06/2021 06:10:00" "Diabetes Mellitus"               "No" 
"6621129" "01/06/2021 06:10:00" "Endocarditis"                    "No" 
"6621129" "01/06/2021 06:10:00" "Heart Failure"                   "Yes"
"6621129" "01/06/2021 06:10:00" "Hostile Chest"                   "No" 
"6621129" "01/06/2021 06:10:00" "Hypertension"                    "Yes"
"6621129" "01/06/2021 06:10:00" "Liver Disease"                   "No" 
"6621129" "01/06/2021 06:10:00" "Myocardial Infarction"           "No" 
"6621129" "01/06/2021 06:10:00" "Peripheral Arterial Disease"     "No" 
"6621129" "01/06/2021 06:10:00" "Porcelain Aorta"                 "No" 
"6621129" "01/06/2021 06:10:00" "Transient Ischemic Attack (TIA)" "No" 
"6621282" "01/06/2021 09:11:00" "Atrial Fibrillation"             "No" 
"6621282" "01/06/2021 09:11:00" "Atrial Flutter"                  "No" 
"6621282" "01/06/2021 09:11:00" "Carotid Artery Stenosis"         "No" 
"6621282" "01/06/2021 09:11:00" "Cerebrovascular Accident"        "No" 
"6621282" "01/06/2021 09:11:00" "Cerebrovascular Disease"         "Yes"
"6621282" "01/06/2021 09:11:00" "Chronic Lung Disease"            "Yes"
"6621282" "01/06/2021 09:11:00" "Conduction Defect"               "No" 
"6621282" "01/06/2021 09:11:00" "Dementia - Moderate to Severe"   "No" 
"6621282" "01/06/2021 09:11:00" "Diabetes Mellitus"               "No" 
"6621282" "01/06/2021 09:11:00" "Endocarditis"                    "No" 
"6621282" "01/06/2021 09:11:00" "Heart Failure"                   "No" 
"6621282" "01/06/2021 09:11:00" "Hostile Chest"                   "No" 
"6621282" "01/06/2021 09:11:00" "Hypertension"                    "Yes"
"6621282" "01/06/2021 09:11:00" "Liver Disease"                   "No" 
"6621282" "01/06/2021 09:11:00" "Myocardial Infarction"           "No" 
"6621282" "01/06/2021 09:11:00" "Peripheral Arterial Disease"     "Yes"
"6621282" "01/06/2021 09:11:00" "Porcelain Aorta"                 "No" 
"6621282" "01/06/2021 09:11:00" "Transient Ischemic Attack (TIA)" "No" 
"6642109" "01/07/2021 07:15:00" "Atrial Fibrillation"             "Yes"
"6642109" "01/07/2021 07:15:00" "Atrial Flutter"                  "No" 
"6642109" "01/07/2021 07:15:00" "Cardiomyopathy"                  "No" 
"6642109" "01/07/2021 07:15:00" "Carotid Artery Stenosis"         "Yes"
"6642109" "01/07/2021 07:15:00" "Cerebrovascular Accident"        "No" 
"6642109" "01/07/2021 07:15:00" "Cerebrovascular Disease"         "No" 
"6642109" "01/07/2021 07:15:00" "Chronic Lung Disease"            "Yes"
"6642109" "01/07/2021 07:15:00" "Dementia - Moderate to Severe"   "No" 
"6642109" "01/07/2021 07:15:00" "Diabetes Mellitus"               "No" 
"6642109" "01/07/2021 07:15:00" "Endocarditis"                    "No" 
"6642109" "01/07/2021 07:15:00" "Heart Failure"                   "No" 
"6642109" "01/07/2021 07:15:00" "Hostile Chest"                   "Yes"
"6642109" "01/07/2021 07:15:00" "Hypertension"                    "Yes"
"6642109" "01/07/2021 07:15:00" "Liver Disease"                   "No" 
"6642109" "01/07/2021 07:15:00" "Myocardial Infarction"           "No" 
"6642109" "01/07/2021 07:15:00" "Peripheral Arterial Disease"     "Yes"
"6642109" "01/07/2021 07:15:00" "Porcelain Aorta"                 "No" 
"6642109" "01/07/2021 07:15:00" "Transient Ischemic Attack (TIA)" "No" 
"6642462" "01/07/2021 13:02:00" "Atrial Fibrillation"             "No" 
"6642462" "01/07/2021 13:02:00" "Atrial Flutter"                  "No" 
"6642462" "01/07/2021 13:02:00" "Cardiomyopathy"                  "No" 
"6642462" "01/07/2021 13:02:00" "Carotid Artery Stenosis"         "No" 
"6642462" "01/07/2021 13:02:00" "Cerebrovascular Accident"        "No" 
"6642462" "01/07/2021 13:02:00" "Cerebrovascular Disease"         "No" 
"6642462" "01/07/2021 13:02:00" "Chronic Lung Disease"            "No" 
"6642462" "01/07/2021 13:02:00" "Dementia - Moderate to Severe"   "No" 
"6642462" "01/07/2021 13:02:00" "Diabetes Mellitus"               "Yes"
"6642462" "01/07/2021 13:02:00" "Endocarditis"                    "No" 
"6642462" "01/07/2021 13:02:00" "Heart Failure"                   "Yes"
"6642462" "01/07/2021 13:02:00" "Hostile Chest"                   "No" 
"6642462" "01/07/2021 13:02:00" "Hypertension"                    "No" 
"6642462" "01/07/2021 13:02:00" "Liver Disease"                   "No" 
"6642462" "01/07/2021 13:02:00" "Myocardial Infarction"           "No" 
"6642462" "01/07/2021 13:02:00" "Peripheral Arterial Disease"     "No" 
"6642462" "01/07/2021 13:02:00" "Porcelain Aorta"                 "No" 
"6642462" "01/07/2021 13:02:00" "Transient Ischemic Attack (TIA)" "No" 
"6642700" "01/07/2021 09:46:00" "Atrial Fibrillation"             "No" 
"6642700" "01/07/2021 09:46:00" "Atrial Flutter"                  "No" 
"6642700" "01/07/2021 09:46:00" "Carotid Artery Stenosis"         "No" 
"6642700" "01/07/2021 09:46:00" "Cerebrovascular Accident"        "No" 
"6642700" "01/07/2021 09:46:00" "Cerebrovascular Disease"         "No" 
"6642700" "01/07/2021 09:46:00" "Chronic Lung Disease"            "No" 
"6642700" "01/07/2021 09:46:00" "Conduction Defect"               "No" 
"6642700" "01/07/2021 09:46:00" "Dementia - Moderate to Severe"   "No" 
"6642700" "01/07/2021 09:46:00" "Diabetes Mellitus"               "Yes"
"6642700" "01/07/2021 09:46:00" "Endocarditis"                    "No" 
"6642700" "01/07/2021 09:46:00" "Heart Failure"                   "No" 
"6642700" "01/07/2021 09:46:00" "Hostile Chest"                   "No" 
"6642700" "01/07/2021 09:46:00" "Hypertension"                    "Yes"
"6642700" "01/07/2021 09:46:00" "Liver Disease"                   "No" 
"6642700" "01/07/2021 09:46:00" "Myocardial Infarction"           "No" 
"6642700" "01/07/2021 09:46:00" "Peripheral Arterial Disease"     "No" 
"6642700" "01/07/2021 09:46:00" "Porcelain Aorta"                 "No" 
"6642700" "01/07/2021 09:46:00" "Transient Ischemic Attack (TIA)" "Yes"
"6642858" "01/11/2021 05:44:00" "Atrial Fibrillation"             "No" 
"6642858" "01/11/2021 05:44:00" "Atrial Flutter"                  "No" 
"6642858" "01/11/2021 05:44:00" "Carotid Artery Stenosis"         "No" 
"6642858" "01/11/2021 05:44:00" "Cerebrovascular Accident"        "No" 
"6642858" "01/11/2021 05:44:00" "Cerebrovascular Disease"         "Yes"
"6642858" "01/11/2021 05:44:00" "Chronic Lung Disease"            "Yes"
"6642858" "01/11/2021 05:44:00" "Conduction Defect"               "No" 
"6642858" "01/11/2021 05:44:00" "Dementia - Moderate to Severe"   "No" 
"6642858" "01/11/2021 05:44:00" "Diabetes Mellitus"               "No" 
"6642858" "01/11/2021 05:44:00" "Endocarditis"                    "No" 
"6642858" "01/11/2021 05:44:00" "Heart Failure"                   "No" 
"6642858" "01/11/2021 05:44:00" "Hostile Chest"                   "No" 
"6642858" "01/11/2021 05:44:00" "Hypertension"                    "Yes"
"6642858" "01/11/2021 05:44:00" "Liver Disease"                   "No" 
"6642858" "01/11/2021 05:44:00" "Myocardial Infarction"           "No" 
"6642858" "01/11/2021 05:44:00" "Peripheral Arterial Disease"     "Yes"
"6642858" "01/11/2021 05:44:00" "Porcelain Aorta"                 "No" 
"6642858" "01/11/2021 05:44:00" "Transient Ischemic Attack (TIA)" "No" 
"6643743" "01/08/2021 09:06:00" "Atrial Fibrillation"             "No" 
"6643743" "01/08/2021 09:06:00" "Atrial Flutter"                  "No" 
"6643743" "01/08/2021 09:06:00" "Carotid Artery Stenosis"         "No" 
"6643743" "01/08/2021 09:06:00" "Cerebrovascular Accident"        "No" 
"6643743" "01/08/2021 09:06:00" "Cerebrovascular Disease"         "No" 
"6643743" "01/08/2021 09:06:00" "Chronic Lung Disease"            "No" 
"6643743" "01/08/2021 09:06:00" "Conduction Defect"               "No" 
"6643743" "01/08/2021 09:06:00" "Dementia - Moderate to Severe"   "No" 
"6643743" "01/08/2021 09:06:00" "Diabetes Mellitus"               "Yes"
"6643743" "01/08/2021 09:06:00" "Endocarditis"                    "No" 
"6643743" "01/08/2021 09:06:00" "Heart Failure"                   "Yes"
"6643743" "01/08/2021 09:06:00" "Hostile Chest"                   "No" 
"6643743" "01/08/2021 09:06:00" "Hypertension"                    "Yes"
"6643743" "01/08/2021 09:06:00" "Liver Disease"                   "No" 
"6643743" "01/08/2021 09:06:00" "Myocardial Infarction"           "No" 
"6643743" "01/08/2021 09:06:00" "Peripheral Arterial Disease"     "No" 
"6643743" "01/08/2021 09:06:00" "Porcelain Aorta"                 "No" 
"6643743" "01/08/2021 09:06:00" "Transient Ischemic Attack (TIA)" "No" 
"6645572" "01/13/2021 06:13:00" "Atrial Fibrillation"             "No" 
"6645572" "01/13/2021 06:13:00" "Atrial Flutter"                  "No" 
"6645572" "01/13/2021 06:13:00" "Carotid Artery Stenosis"         "No" 
"6645572" "01/13/2021 06:13:00" "Cerebrovascular Accident"        "No" 
"6645572" "01/13/2021 06:13:00" "Cerebrovascular Disease"         "No" 
"6645572" "01/13/2021 06:13:00" "Chronic Lung Disease"            "Yes"
"6645572" "01/13/2021 06:13:00" "Conduction Defect"               "No" 
"6645572" "01/13/2021 06:13:00" "Dementia - Moderate to Severe"   "No" 
"6645572" "01/13/2021 06:13:00" "Diabetes Mellitus"               "No" 
"6645572" "01/13/2021 06:13:00" "Endocarditis"                    "No" 
"6645572" "01/13/2021 06:13:00" "Heart Failure"                   "Yes"
"6645572" "01/13/2021 06:13:00" "Hostile Chest"                   "No" 
"6645572" "01/13/2021 06:13:00" "Hypertension"                    "Yes"
"6645572" "01/13/2021 06:13:00" "Liver Disease"                   "No" 
"6645572" "01/13/2021 06:13:00" "Myocardial Infarction"           "No" 
"6645572" "01/13/2021 06:13:00" "Peripheral Arterial Disease"     "Yes"
"6645572" "01/13/2021 06:13:00" "Porcelain Aorta"                 "No" 
"6645572" "01/13/2021 06:13:00" "Transient Ischemic Attack (TIA)" "No" 
end
    Tags: None
  • #2
    Do you mean something like this:

    Code:
        * Example generated by -dataex-. For more info, type help dataex
    clear
    input str7 ncdrpatientid str19 arrivaldatetime str31 conditionhx str3 conditionhxoccurence
    "6620408" "12/30/2020 05:50:00" "Atrial Fibrillation"             "Yes"
    "6620408" "12/30/2020 05:50:00" "Atrial Flutter"                  "No" 
    "6620408" "12/30/2020 05:50:00" "Carotid Artery Stenosis"         "No" 
    "6620408" "12/30/2020 05:50:00" "Cerebrovascular Accident"        "No" 
    "6620408" "12/30/2020 05:50:00" "Cerebrovascular Disease"         "No" 
    "6620408" "12/30/2020 05:50:00" "Chronic Lung Disease"            "Yes"
    "6620408" "12/30/2020 05:50:00" "Conduction Defect"               "No" 
    "6620408" "12/30/2020 05:50:00" "Dementia - Moderate to Severe"   "No" 
    "6620408" "12/30/2020 05:50:00" "Diabetes Mellitus"               "Yes"
    "6620408" "12/30/2020 05:50:00" "Endocarditis"                    "No" 
    "6620408" "12/30/2020 05:50:00" "Heart Failure"                   "Yes"
    "6620408" "12/30/2020 05:50:00" "Hostile Chest"                   "No" 
    "6620408" "12/30/2020 05:50:00" "Hypertension"                    "Yes"
    "6620408" "12/30/2020 05:50:00" "Liver Disease"                   "No" 
    "6620408" "12/30/2020 05:50:00" "Myocardial Infarction"           "No" 
    "6620408" "12/30/2020 05:50:00" "Peripheral Arterial Disease"     "No" 
    "6620408" "12/30/2020 05:50:00" "Porcelain Aorta"                 "No" 
    "6620408" "12/30/2020 05:50:00" "Transient Ischemic Attack (TIA)" "No" 
    "6547037" "10/27/2020 18:09:00" "Atrial Fibrillation"             "No" 
    "6547037" "10/27/2020 18:09:00" "Atrial Flutter"                  "No" 
    "6547037" "10/27/2020 18:09:00" "Carotid Artery Stenosis"         "No" 
    "6547037" "10/27/2020 18:09:00" "Cerebrovascular Accident"        "No" 
    "6547037" "10/27/2020 18:09:00" "Cerebrovascular Disease"         "No" 
    "6547037" "10/27/2020 18:09:00" "Chronic Lung Disease"            "No" 
    "6547037" "10/27/2020 18:09:00" "Conduction Defect"               "Yes"
    "6547037" "10/27/2020 18:09:00" "Dementia - Moderate to Severe"   "No" 
    "6547037" "10/27/2020 18:09:00" "Diabetes Mellitus"               "No" 
    "6547037" "10/27/2020 18:09:00" "Endocarditis"                    "No" 
    "6547037" "10/27/2020 18:09:00" "Heart Failure"                   "Yes"
    "6547037" "10/27/2020 18:09:00" "Hostile Chest"                   "No" 
    "6547037" "10/27/2020 18:09:00" "Hypertension"                    "No" 
    "6547037" "10/27/2020 18:09:00" "Liver Disease"                   "No" 
    "6547037" "10/27/2020 18:09:00" "Myocardial Infarction"           "No" 
    "6547037" "10/27/2020 18:09:00" "Peripheral Arterial Disease"     "No" 
    "6547037" "10/27/2020 18:09:00" "Porcelain Aorta"                 "No" 
    "6547037" "10/27/2020 18:09:00" "Transient Ischemic Attack (TIA)" "No" 
    "6621129" "01/06/2021 06:10:00" "Atrial Fibrillation"             "Yes"
    "6621129" "01/06/2021 06:10:00" "Atrial Flutter"                  "No" 
    "6621129" "01/06/2021 06:10:00" "Carotid Artery Stenosis"         "No" 
    "6621129" "01/06/2021 06:10:00" "Cerebrovascular Accident"        "No" 
    "6621129" "01/06/2021 06:10:00" "Cerebrovascular Disease"         "No" 
    "6621129" "01/06/2021 06:10:00" "Chronic Lung Disease"            "No" 
    "6621129" "01/06/2021 06:10:00" "Conduction Defect"               "No" 
    "6621129" "01/06/2021 06:10:00" "Dementia - Moderate to Severe"   "No" 
    "6621129" "01/06/2021 06:10:00" "Diabetes Mellitus"               "No" 
    "6621129" "01/06/2021 06:10:00" "Endocarditis"                    "No" 
    "6621129" "01/06/2021 06:10:00" "Heart Failure"                   "Yes"
    "6621129" "01/06/2021 06:10:00" "Hostile Chest"                   "No" 
    "6621129" "01/06/2021 06:10:00" "Hypertension"                    "Yes"
    "6621129" "01/06/2021 06:10:00" "Liver Disease"                   "No" 
    "6621129" "01/06/2021 06:10:00" "Myocardial Infarction"           "No" 
    "6621129" "01/06/2021 06:10:00" "Peripheral Arterial Disease"     "No" 
    "6621129" "01/06/2021 06:10:00" "Porcelain Aorta"                 "No" 
    "6621129" "01/06/2021 06:10:00" "Transient Ischemic Attack (TIA)" "No" 
    "6621282" "01/06/2021 09:11:00" "Atrial Fibrillation"             "No" 
    "6621282" "01/06/2021 09:11:00" "Atrial Flutter"                  "No" 
    "6621282" "01/06/2021 09:11:00" "Carotid Artery Stenosis"         "No" 
    "6621282" "01/06/2021 09:11:00" "Cerebrovascular Accident"        "No" 
    "6621282" "01/06/2021 09:11:00" "Cerebrovascular Disease"         "Yes"
    "6621282" "01/06/2021 09:11:00" "Chronic Lung Disease"            "Yes"
    "6621282" "01/06/2021 09:11:00" "Conduction Defect"               "No" 
    "6621282" "01/06/2021 09:11:00" "Dementia - Moderate to Severe"   "No" 
    "6621282" "01/06/2021 09:11:00" "Diabetes Mellitus"               "No" 
    "6621282" "01/06/2021 09:11:00" "Endocarditis"                    "No" 
    "6621282" "01/06/2021 09:11:00" "Heart Failure"                   "No" 
    "6621282" "01/06/2021 09:11:00" "Hostile Chest"                   "No" 
    "6621282" "01/06/2021 09:11:00" "Hypertension"                    "Yes"
    "6621282" "01/06/2021 09:11:00" "Liver Disease"                   "No" 
    "6621282" "01/06/2021 09:11:00" "Myocardial Infarction"           "No" 
    "6621282" "01/06/2021 09:11:00" "Peripheral Arterial Disease"     "Yes"
    "6621282" "01/06/2021 09:11:00" "Porcelain Aorta"                 "No" 
    "6621282" "01/06/2021 09:11:00" "Transient Ischemic Attack (TIA)" "No" 
    "6642109" "01/07/2021 07:15:00" "Atrial Fibrillation"             "Yes"
    "6642109" "01/07/2021 07:15:00" "Atrial Flutter"                  "No" 
    "6642109" "01/07/2021 07:15:00" "Cardiomyopathy"                  "No" 
    "6642109" "01/07/2021 07:15:00" "Carotid Artery Stenosis"         "Yes"
    "6642109" "01/07/2021 07:15:00" "Cerebrovascular Accident"        "No" 
    "6642109" "01/07/2021 07:15:00" "Cerebrovascular Disease"         "No" 
    "6642109" "01/07/2021 07:15:00" "Chronic Lung Disease"            "Yes"
    "6642109" "01/07/2021 07:15:00" "Dementia - Moderate to Severe"   "No" 
    "6642109" "01/07/2021 07:15:00" "Diabetes Mellitus"               "No" 
    "6642109" "01/07/2021 07:15:00" "Endocarditis"                    "No" 
    "6642109" "01/07/2021 07:15:00" "Heart Failure"                   "No" 
    "6642109" "01/07/2021 07:15:00" "Hostile Chest"                   "Yes"
    "6642109" "01/07/2021 07:15:00" "Hypertension"                    "Yes"
    "6642109" "01/07/2021 07:15:00" "Liver Disease"                   "No" 
    "6642109" "01/07/2021 07:15:00" "Myocardial Infarction"           "No" 
    "6642109" "01/07/2021 07:15:00" "Peripheral Arterial Disease"     "Yes"
    "6642109" "01/07/2021 07:15:00" "Porcelain Aorta"                 "No" 
    "6642109" "01/07/2021 07:15:00" "Transient Ischemic Attack (TIA)" "No" 
    "6642462" "01/07/2021 13:02:00" "Atrial Fibrillation"             "No" 
    "6642462" "01/07/2021 13:02:00" "Atrial Flutter"                  "No" 
    "6642462" "01/07/2021 13:02:00" "Cardiomyopathy"                  "No" 
    "6642462" "01/07/2021 13:02:00" "Carotid Artery Stenosis"         "No" 
    "6642462" "01/07/2021 13:02:00" "Cerebrovascular Accident"        "No" 
    "6642462" "01/07/2021 13:02:00" "Cerebrovascular Disease"         "No" 
    "6642462" "01/07/2021 13:02:00" "Chronic Lung Disease"            "No" 
    "6642462" "01/07/2021 13:02:00" "Dementia - Moderate to Severe"   "No" 
    "6642462" "01/07/2021 13:02:00" "Diabetes Mellitus"               "Yes"
    "6642462" "01/07/2021 13:02:00" "Endocarditis"                    "No" 
    "6642462" "01/07/2021 13:02:00" "Heart Failure"                   "Yes"
    "6642462" "01/07/2021 13:02:00" "Hostile Chest"                   "No" 
    "6642462" "01/07/2021 13:02:00" "Hypertension"                    "No" 
    "6642462" "01/07/2021 13:02:00" "Liver Disease"                   "No" 
    "6642462" "01/07/2021 13:02:00" "Myocardial Infarction"           "No" 
    "6642462" "01/07/2021 13:02:00" "Peripheral Arterial Disease"     "No" 
    "6642462" "01/07/2021 13:02:00" "Porcelain Aorta"                 "No" 
    "6642462" "01/07/2021 13:02:00" "Transient Ischemic Attack (TIA)" "No" 
    "6642700" "01/07/2021 09:46:00" "Atrial Fibrillation"             "No" 
    "6642700" "01/07/2021 09:46:00" "Atrial Flutter"                  "No" 
    "6642700" "01/07/2021 09:46:00" "Carotid Artery Stenosis"         "No" 
    "6642700" "01/07/2021 09:46:00" "Cerebrovascular Accident"        "No" 
    "6642700" "01/07/2021 09:46:00" "Cerebrovascular Disease"         "No" 
    "6642700" "01/07/2021 09:46:00" "Chronic Lung Disease"            "No" 
    "6642700" "01/07/2021 09:46:00" "Conduction Defect"               "No" 
    "6642700" "01/07/2021 09:46:00" "Dementia - Moderate to Severe"   "No" 
    "6642700" "01/07/2021 09:46:00" "Diabetes Mellitus"               "Yes"
    "6642700" "01/07/2021 09:46:00" "Endocarditis"                    "No" 
    "6642700" "01/07/2021 09:46:00" "Heart Failure"                   "No" 
    "6642700" "01/07/2021 09:46:00" "Hostile Chest"                   "No" 
    "6642700" "01/07/2021 09:46:00" "Hypertension"                    "Yes"
    "6642700" "01/07/2021 09:46:00" "Liver Disease"                   "No" 
    "6642700" "01/07/2021 09:46:00" "Myocardial Infarction"           "No" 
    "6642700" "01/07/2021 09:46:00" "Peripheral Arterial Disease"     "No" 
    "6642700" "01/07/2021 09:46:00" "Porcelain Aorta"                 "No" 
    "6642700" "01/07/2021 09:46:00" "Transient Ischemic Attack (TIA)" "Yes"
    "6642858" "01/11/2021 05:44:00" "Atrial Fibrillation"             "No" 
    "6642858" "01/11/2021 05:44:00" "Atrial Flutter"                  "No" 
    "6642858" "01/11/2021 05:44:00" "Carotid Artery Stenosis"         "No" 
    "6642858" "01/11/2021 05:44:00" "Cerebrovascular Accident"        "No" 
    "6642858" "01/11/2021 05:44:00" "Cerebrovascular Disease"         "Yes"
    "6642858" "01/11/2021 05:44:00" "Chronic Lung Disease"            "Yes"
    "6642858" "01/11/2021 05:44:00" "Conduction Defect"               "No" 
    "6642858" "01/11/2021 05:44:00" "Dementia - Moderate to Severe"   "No" 
    "6642858" "01/11/2021 05:44:00" "Diabetes Mellitus"               "No" 
    "6642858" "01/11/2021 05:44:00" "Endocarditis"                    "No" 
    "6642858" "01/11/2021 05:44:00" "Heart Failure"                   "No" 
    "6642858" "01/11/2021 05:44:00" "Hostile Chest"                   "No" 
    "6642858" "01/11/2021 05:44:00" "Hypertension"                    "Yes"
    "6642858" "01/11/2021 05:44:00" "Liver Disease"                   "No" 
    "6642858" "01/11/2021 05:44:00" "Myocardial Infarction"           "No" 
    "6642858" "01/11/2021 05:44:00" "Peripheral Arterial Disease"     "Yes"
    "6642858" "01/11/2021 05:44:00" "Porcelain Aorta"                 "No" 
    "6642858" "01/11/2021 05:44:00" "Transient Ischemic Attack (TIA)" "No" 
    "6643743" "01/08/2021 09:06:00" "Atrial Fibrillation"             "No" 
    "6643743" "01/08/2021 09:06:00" "Atrial Flutter"                  "No" 
    "6643743" "01/08/2021 09:06:00" "Carotid Artery Stenosis"         "No" 
    "6643743" "01/08/2021 09:06:00" "Cerebrovascular Accident"        "No" 
    "6643743" "01/08/2021 09:06:00" "Cerebrovascular Disease"         "No" 
    "6643743" "01/08/2021 09:06:00" "Chronic Lung Disease"            "No" 
    "6643743" "01/08/2021 09:06:00" "Conduction Defect"               "No" 
    "6643743" "01/08/2021 09:06:00" "Dementia - Moderate to Severe"   "No" 
    "6643743" "01/08/2021 09:06:00" "Diabetes Mellitus"               "Yes"
    "6643743" "01/08/2021 09:06:00" "Endocarditis"                    "No" 
    "6643743" "01/08/2021 09:06:00" "Heart Failure"                   "Yes"
    "6643743" "01/08/2021 09:06:00" "Hostile Chest"                   "No" 
    "6643743" "01/08/2021 09:06:00" "Hypertension"                    "Yes"
    "6643743" "01/08/2021 09:06:00" "Liver Disease"                   "No" 
    "6643743" "01/08/2021 09:06:00" "Myocardial Infarction"           "No" 
    "6643743" "01/08/2021 09:06:00" "Peripheral Arterial Disease"     "No" 
    "6643743" "01/08/2021 09:06:00" "Porcelain Aorta"                 "No" 
    "6643743" "01/08/2021 09:06:00" "Transient Ischemic Attack (TIA)" "No" 
    "6645572" "01/13/2021 06:13:00" "Atrial Fibrillation"             "No" 
    "6645572" "01/13/2021 06:13:00" "Atrial Flutter"                  "No" 
    "6645572" "01/13/2021 06:13:00" "Carotid Artery Stenosis"         "No" 
    "6645572" "01/13/2021 06:13:00" "Cerebrovascular Accident"        "No" 
    "6645572" "01/13/2021 06:13:00" "Cerebrovascular Disease"         "No" 
    "6645572" "01/13/2021 06:13:00" "Chronic Lung Disease"            "Yes"
    "6645572" "01/13/2021 06:13:00" "Conduction Defect"               "No" 
    "6645572" "01/13/2021 06:13:00" "Dementia - Moderate to Severe"   "No" 
    "6645572" "01/13/2021 06:13:00" "Diabetes Mellitus"               "No" 
    "6645572" "01/13/2021 06:13:00" "Endocarditis"                    "No" 
    "6645572" "01/13/2021 06:13:00" "Heart Failure"                   "Yes"
    "6645572" "01/13/2021 06:13:00" "Hostile Chest"                   "No" 
    "6645572" "01/13/2021 06:13:00" "Hypertension"                    "Yes"
    "6645572" "01/13/2021 06:13:00" "Liver Disease"                   "No" 
    "6645572" "01/13/2021 06:13:00" "Myocardial Infarction"           "No" 
    "6645572" "01/13/2021 06:13:00" "Peripheral Arterial Disease"     "Yes"
    "6645572" "01/13/2021 06:13:00" "Porcelain Aorta"                 "No" 
    "6645572" "01/13/2021 06:13:00" "Transient Ischemic Attack (TIA)" "No" 
    end
    
    
    * Convert date/time to readable format
    generate double arrival_datetime = clock(arrivaldatetime, "MDYhms"), after(arrivaldatetime)
    format arrival_datetime %tc
    drop arrivaldatetime
    
    * Sort the data
    sort ncdrpatientid arrival_datetime, stable
    
    * Remove dashes from conditionhx
    replace conditionhx = trim(itrim(subinstr(conditionhx, "-", "", .)))

    * Shortening a few names (As variables cant be more than 32 chars)
    replace conditionhx = trim(itrim(subinstr(conditionhx, "(TIA)", "", .)))
    
    * Replace spaces with underscores
    replace conditionhx = subinstr(conditionhx, " ", "_", .)
    
    * Reshape data to wide format
    rename conditionhxoccurence y_
    reshape wide y_, i(ncdrpatientid arrival_datetime) j(conditionhx) string
    rename (y_*) (*)

    Comment

    • #3
      Thanks for the very clear data example. There is quite a lot to do here, but I don't see a role for stack: the data are already stacked and you need rather reshape wide. There is plenty of scope for working on variable names and display formats still.


      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input str7 ncdrpatientid str19 arrivaldatetime str31 conditionhx str3 conditionhxoccurence
"6620408" "12/30/2020 05:50:00" "Atrial Fibrillation"             "Yes"
"6620408" "12/30/2020 05:50:00" "Atrial Flutter"                  "No"
"6620408" "12/30/2020 05:50:00" "Carotid Artery Stenosis"         "No"
"6620408" "12/30/2020 05:50:00" "Cerebrovascular Accident"        "No"
"6620408" "12/30/2020 05:50:00" "Cerebrovascular Disease"         "No"
"6620408" "12/30/2020 05:50:00" "Chronic Lung Disease"            "Yes"
"6620408" "12/30/2020 05:50:00" "Conduction Defect"               "No"
"6620408" "12/30/2020 05:50:00" "Dementia - Moderate to Severe"   "No"
"6620408" "12/30/2020 05:50:00" "Diabetes Mellitus"               "Yes"
"6620408" "12/30/2020 05:50:00" "Endocarditis"                    "No"
"6620408" "12/30/2020 05:50:00" "Heart Failure"                   "Yes"
"6620408" "12/30/2020 05:50:00" "Hostile Chest"                   "No"
"6620408" "12/30/2020 05:50:00" "Hypertension"                    "Yes"
"6620408" "12/30/2020 05:50:00" "Liver Disease"                   "No"
"6620408" "12/30/2020 05:50:00" "Myocardial Infarction"           "No"
"6620408" "12/30/2020 05:50:00" "Peripheral Arterial Disease"     "No"
"6620408" "12/30/2020 05:50:00" "Porcelain Aorta"                 "No"
"6620408" "12/30/2020 05:50:00" "Transient Ischemic Attack (TIA)" "No"
"6547037" "10/27/2020 18:09:00" "Atrial Fibrillation"             "No"
"6547037" "10/27/2020 18:09:00" "Atrial Flutter"                  "No"
"6547037" "10/27/2020 18:09:00" "Carotid Artery Stenosis"         "No"
"6547037" "10/27/2020 18:09:00" "Cerebrovascular Accident"        "No"
"6547037" "10/27/2020 18:09:00" "Cerebrovascular Disease"         "No"
"6547037" "10/27/2020 18:09:00" "Chronic Lung Disease"            "No"
"6547037" "10/27/2020 18:09:00" "Conduction Defect"               "Yes"
"6547037" "10/27/2020 18:09:00" "Dementia - Moderate to Severe"   "No"
"6547037" "10/27/2020 18:09:00" "Diabetes Mellitus"               "No"
"6547037" "10/27/2020 18:09:00" "Endocarditis"                    "No"
"6547037" "10/27/2020 18:09:00" "Heart Failure"                   "Yes"
"6547037" "10/27/2020 18:09:00" "Hostile Chest"                   "No"
"6547037" "10/27/2020 18:09:00" "Hypertension"                    "No"
"6547037" "10/27/2020 18:09:00" "Liver Disease"                   "No"
"6547037" "10/27/2020 18:09:00" "Myocardial Infarction"           "No"
"6547037" "10/27/2020 18:09:00" "Peripheral Arterial Disease"     "No"
"6547037" "10/27/2020 18:09:00" "Porcelain Aorta"                 "No"
"6547037" "10/27/2020 18:09:00" "Transient Ischemic Attack (TIA)" "No"
"6621129" "01/06/2021 06:10:00" "Atrial Fibrillation"             "Yes"
"6621129" "01/06/2021 06:10:00" "Atrial Flutter"                  "No"
"6621129" "01/06/2021 06:10:00" "Carotid Artery Stenosis"         "No"
"6621129" "01/06/2021 06:10:00" "Cerebrovascular Accident"        "No"
"6621129" "01/06/2021 06:10:00" "Cerebrovascular Disease"         "No"
"6621129" "01/06/2021 06:10:00" "Chronic Lung Disease"            "No"
"6621129" "01/06/2021 06:10:00" "Conduction Defect"               "No"
"6621129" "01/06/2021 06:10:00" "Dementia - Moderate to Severe"   "No"
"6621129" "01/06/2021 06:10:00" "Diabetes Mellitus"               "No"
"6621129" "01/06/2021 06:10:00" "Endocarditis"                    "No"
"6621129" "01/06/2021 06:10:00" "Heart Failure"                   "Yes"
"6621129" "01/06/2021 06:10:00" "Hostile Chest"                   "No"
"6621129" "01/06/2021 06:10:00" "Hypertension"                    "Yes"
"6621129" "01/06/2021 06:10:00" "Liver Disease"                   "No"
"6621129" "01/06/2021 06:10:00" "Myocardial Infarction"           "No"
"6621129" "01/06/2021 06:10:00" "Peripheral Arterial Disease"     "No"
"6621129" "01/06/2021 06:10:00" "Porcelain Aorta"                 "No"
"6621129" "01/06/2021 06:10:00" "Transient Ischemic Attack (TIA)" "No"
"6621282" "01/06/2021 09:11:00" "Atrial Fibrillation"             "No"
"6621282" "01/06/2021 09:11:00" "Atrial Flutter"                  "No"
"6621282" "01/06/2021 09:11:00" "Carotid Artery Stenosis"         "No"
"6621282" "01/06/2021 09:11:00" "Cerebrovascular Accident"        "No"
"6621282" "01/06/2021 09:11:00" "Cerebrovascular Disease"         "Yes"
"6621282" "01/06/2021 09:11:00" "Chronic Lung Disease"            "Yes"
"6621282" "01/06/2021 09:11:00" "Conduction Defect"               "No"
"6621282" "01/06/2021 09:11:00" "Dementia - Moderate to Severe"   "No"
"6621282" "01/06/2021 09:11:00" "Diabetes Mellitus"               "No"
"6621282" "01/06/2021 09:11:00" "Endocarditis"                    "No"
"6621282" "01/06/2021 09:11:00" "Heart Failure"                   "No"
"6621282" "01/06/2021 09:11:00" "Hostile Chest"                   "No"
"6621282" "01/06/2021 09:11:00" "Hypertension"                    "Yes"
"6621282" "01/06/2021 09:11:00" "Liver Disease"                   "No"
"6621282" "01/06/2021 09:11:00" "Myocardial Infarction"           "No"
"6621282" "01/06/2021 09:11:00" "Peripheral Arterial Disease"     "Yes"
"6621282" "01/06/2021 09:11:00" "Porcelain Aorta"                 "No"
"6621282" "01/06/2021 09:11:00" "Transient Ischemic Attack (TIA)" "No"
"6642109" "01/07/2021 07:15:00" "Atrial Fibrillation"             "Yes"
"6642109" "01/07/2021 07:15:00" "Atrial Flutter"                  "No"
"6642109" "01/07/2021 07:15:00" "Cardiomyopathy"                  "No"
"6642109" "01/07/2021 07:15:00" "Carotid Artery Stenosis"         "Yes"
"6642109" "01/07/2021 07:15:00" "Cerebrovascular Accident"        "No"
"6642109" "01/07/2021 07:15:00" "Cerebrovascular Disease"         "No"
"6642109" "01/07/2021 07:15:00" "Chronic Lung Disease"            "Yes"
"6642109" "01/07/2021 07:15:00" "Dementia - Moderate to Severe"   "No"
"6642109" "01/07/2021 07:15:00" "Diabetes Mellitus"               "No"
"6642109" "01/07/2021 07:15:00" "Endocarditis"                    "No"
"6642109" "01/07/2021 07:15:00" "Heart Failure"                   "No"
"6642109" "01/07/2021 07:15:00" "Hostile Chest"                   "Yes"
"6642109" "01/07/2021 07:15:00" "Hypertension"                    "Yes"
"6642109" "01/07/2021 07:15:00" "Liver Disease"                   "No"
"6642109" "01/07/2021 07:15:00" "Myocardial Infarction"           "No"
"6642109" "01/07/2021 07:15:00" "Peripheral Arterial Disease"     "Yes"
"6642109" "01/07/2021 07:15:00" "Porcelain Aorta"                 "No"
"6642109" "01/07/2021 07:15:00" "Transient Ischemic Attack (TIA)" "No"
"6642462" "01/07/2021 13:02:00" "Atrial Fibrillation"             "No"
"6642462" "01/07/2021 13:02:00" "Atrial Flutter"                  "No"
"6642462" "01/07/2021 13:02:00" "Cardiomyopathy"                  "No"
"6642462" "01/07/2021 13:02:00" "Carotid Artery Stenosis"         "No"
"6642462" "01/07/2021 13:02:00" "Cerebrovascular Accident"        "No"
"6642462" "01/07/2021 13:02:00" "Cerebrovascular Disease"         "No"
"6642462" "01/07/2021 13:02:00" "Chronic Lung Disease"            "No"
"6642462" "01/07/2021 13:02:00" "Dementia - Moderate to Severe"   "No"
"6642462" "01/07/2021 13:02:00" "Diabetes Mellitus"               "Yes"
"6642462" "01/07/2021 13:02:00" "Endocarditis"                    "No"
"6642462" "01/07/2021 13:02:00" "Heart Failure"                   "Yes"
"6642462" "01/07/2021 13:02:00" "Hostile Chest"                   "No"
"6642462" "01/07/2021 13:02:00" "Hypertension"                    "No"
"6642462" "01/07/2021 13:02:00" "Liver Disease"                   "No"
"6642462" "01/07/2021 13:02:00" "Myocardial Infarction"           "No"
"6642462" "01/07/2021 13:02:00" "Peripheral Arterial Disease"     "No"
"6642462" "01/07/2021 13:02:00" "Porcelain Aorta"                 "No"
"6642462" "01/07/2021 13:02:00" "Transient Ischemic Attack (TIA)" "No"
"6642700" "01/07/2021 09:46:00" "Atrial Fibrillation"             "No"
"6642700" "01/07/2021 09:46:00" "Atrial Flutter"                  "No"
"6642700" "01/07/2021 09:46:00" "Carotid Artery Stenosis"         "No"
"6642700" "01/07/2021 09:46:00" "Cerebrovascular Accident"        "No"
"6642700" "01/07/2021 09:46:00" "Cerebrovascular Disease"         "No"
"6642700" "01/07/2021 09:46:00" "Chronic Lung Disease"            "No"
"6642700" "01/07/2021 09:46:00" "Conduction Defect"               "No"
"6642700" "01/07/2021 09:46:00" "Dementia - Moderate to Severe"   "No"
"6642700" "01/07/2021 09:46:00" "Diabetes Mellitus"               "Yes"
"6642700" "01/07/2021 09:46:00" "Endocarditis"                    "No"
"6642700" "01/07/2021 09:46:00" "Heart Failure"                   "No"
"6642700" "01/07/2021 09:46:00" "Hostile Chest"                   "No"
"6642700" "01/07/2021 09:46:00" "Hypertension"                    "Yes"
"6642700" "01/07/2021 09:46:00" "Liver Disease"                   "No"
"6642700" "01/07/2021 09:46:00" "Myocardial Infarction"           "No"
"6642700" "01/07/2021 09:46:00" "Peripheral Arterial Disease"     "No"
"6642700" "01/07/2021 09:46:00" "Porcelain Aorta"                 "No"
"6642700" "01/07/2021 09:46:00" "Transient Ischemic Attack (TIA)" "Yes"
"6642858" "01/11/2021 05:44:00" "Atrial Fibrillation"             "No"
"6642858" "01/11/2021 05:44:00" "Atrial Flutter"                  "No"
"6642858" "01/11/2021 05:44:00" "Carotid Artery Stenosis"         "No"
"6642858" "01/11/2021 05:44:00" "Cerebrovascular Accident"        "No"
"6642858" "01/11/2021 05:44:00" "Cerebrovascular Disease"         "Yes"
"6642858" "01/11/2021 05:44:00" "Chronic Lung Disease"            "Yes"
"6642858" "01/11/2021 05:44:00" "Conduction Defect"               "No"
"6642858" "01/11/2021 05:44:00" "Dementia - Moderate to Severe"   "No"
"6642858" "01/11/2021 05:44:00" "Diabetes Mellitus"               "No"
"6642858" "01/11/2021 05:44:00" "Endocarditis"                    "No"
"6642858" "01/11/2021 05:44:00" "Heart Failure"                   "No"
"6642858" "01/11/2021 05:44:00" "Hostile Chest"                   "No"
"6642858" "01/11/2021 05:44:00" "Hypertension"                    "Yes"
"6642858" "01/11/2021 05:44:00" "Liver Disease"                   "No"
"6642858" "01/11/2021 05:44:00" "Myocardial Infarction"           "No"
"6642858" "01/11/2021 05:44:00" "Peripheral Arterial Disease"     "Yes"
"6642858" "01/11/2021 05:44:00" "Porcelain Aorta"                 "No"
"6642858" "01/11/2021 05:44:00" "Transient Ischemic Attack (TIA)" "No"
"6643743" "01/08/2021 09:06:00" "Atrial Fibrillation"             "No"
"6643743" "01/08/2021 09:06:00" "Atrial Flutter"                  "No"
"6643743" "01/08/2021 09:06:00" "Carotid Artery Stenosis"         "No"
"6643743" "01/08/2021 09:06:00" "Cerebrovascular Accident"        "No"
"6643743" "01/08/2021 09:06:00" "Cerebrovascular Disease"         "No"
"6643743" "01/08/2021 09:06:00" "Chronic Lung Disease"            "No"
"6643743" "01/08/2021 09:06:00" "Conduction Defect"               "No"
"6643743" "01/08/2021 09:06:00" "Dementia - Moderate to Severe"   "No"
"6643743" "01/08/2021 09:06:00" "Diabetes Mellitus"               "Yes"
"6643743" "01/08/2021 09:06:00" "Endocarditis"                    "No"
"6643743" "01/08/2021 09:06:00" "Heart Failure"                   "Yes"
"6643743" "01/08/2021 09:06:00" "Hostile Chest"                   "No"
"6643743" "01/08/2021 09:06:00" "Hypertension"                    "Yes"
"6643743" "01/08/2021 09:06:00" "Liver Disease"                   "No"
"6643743" "01/08/2021 09:06:00" "Myocardial Infarction"           "No"
"6643743" "01/08/2021 09:06:00" "Peripheral Arterial Disease"     "No"
"6643743" "01/08/2021 09:06:00" "Porcelain Aorta"                 "No"
"6643743" "01/08/2021 09:06:00" "Transient Ischemic Attack (TIA)" "No"
"6645572" "01/13/2021 06:13:00" "Atrial Fibrillation"             "No"
"6645572" "01/13/2021 06:13:00" "Atrial Flutter"                  "No"
"6645572" "01/13/2021 06:13:00" "Carotid Artery Stenosis"         "No"
"6645572" "01/13/2021 06:13:00" "Cerebrovascular Accident"        "No"
"6645572" "01/13/2021 06:13:00" "Cerebrovascular Disease"         "No"
"6645572" "01/13/2021 06:13:00" "Chronic Lung Disease"            "Yes"
"6645572" "01/13/2021 06:13:00" "Conduction Defect"               "No"
"6645572" "01/13/2021 06:13:00" "Dementia - Moderate to Severe"   "No"
"6645572" "01/13/2021 06:13:00" "Diabetes Mellitus"               "No"
"6645572" "01/13/2021 06:13:00" "Endocarditis"                    "No"
"6645572" "01/13/2021 06:13:00" "Heart Failure"                   "Yes"
"6645572" "01/13/2021 06:13:00" "Hostile Chest"                   "No"
"6645572" "01/13/2021 06:13:00" "Hypertension"                    "Yes"
"6645572" "01/13/2021 06:13:00" "Liver Disease"                   "No"
"6645572" "01/13/2021 06:13:00" "Myocardial Infarction"           "No"
"6645572" "01/13/2021 06:13:00" "Peripheral Arterial Disease"     "Yes"
"6645572" "01/13/2021 06:13:00" "Porcelain Aorta"                 "No"
"6645572" "01/13/2021 06:13:00" "Transient Ischemic Attack (TIA)" "No"
end

encode conditionhx, gen(condition)
su condition, meanonly
local J = r(max)
forval j = 1/`J' {
    local label`j' : label (condition) `j'
}

gen occurrence = conditionhxoccurence == "Yes"
drop conditionhx*
reshape wide occurrence, i(ncdrpatientid arrivaldatetime) j(condition)

forval j = 1/`J' {
    label var occurrence`j' "`label`j''"
    local newname = strtoname("`label`j''")
    capture rename occurrence`j' `newname'
}

destring ncdrpatientid, replace
gen double datetime = clock(arrivaldatetime, "MDY hms")
format datetime %tc  
order ncdrpatientid arrivaldatetime datetime

describe
list ncdr datetime Atrial*

     +-----------------------------------------------------+
     | ncdrpa~d             datetime   Atrial~n   Atrial~r |
     |-----------------------------------------------------|
  1. |  6547037   27oct2020 18:09:00          0          0 |
  2. |  6620408   30dec2020 05:50:00          1          0 |
  3. |  6621129   06jan2021 06:10:00          1          0 |
  4. |  6621282   06jan2021 09:11:00          0          0 |
  5. |  6642109   07jan2021 07:15:00          1          0 |
     |-----------------------------------------------------|
  6. |  6642462   07jan2021 13:02:00          0          0 |
  7. |  6642700   07jan2021 09:46:00          0          0 |
  8. |  6642858   11jan2021 05:44:00          0          0 |
  9. |  6643743   08jan2021 09:06:00          0          0 |
 10. |  6645572   13jan2021 06:13:00          0          0 |
     +-----------------------------------------------------+
      EDIT I didn't see #2 while I was preparing this. Fahad Mirza and I have the same main strategy. I like my variable labels better and I go as far as numeric indicator variables. The latter could all be assigned value labels 1 "Yes" 0 "No".

      These are my variable labels:

      Code:
      Atrial_Fibril~n float   %9.0g                 Atrial Fibrillation
Atrial_Flutter  float   %9.0g                 Atrial Flutter
Cardiomyopathy  float   %9.0g                 Cardiomyopathy
Carotid_Arter~s float   %9.0g                 Carotid Artery Stenosis
Cerebrovascul~t float   %9.0g                 Cerebrovascular Accident
Cerebrovascul~e float   %9.0g                 Cerebrovascular Disease
Chronic_Lung_~e float   %9.0g                 Chronic Lung Disease
Conduction_De~t float   %9.0g                 Conduction Defect
Dementia___Mo~e float   %9.0g                 Dementia - Moderate to Severe
Diabetes_Mell~s float   %9.0g                 Diabetes Mellitus
Endocarditis    float   %9.0g                 Endocarditis
Heart_Failure   float   %9.0g                 Heart Failure
Hostile_Chest   float   %9.0g                 Hostile Chest
Hypertension    float   %9.0g                 Hypertension
Liver_Disease   float   %9.0g                 Liver Disease
Myocardial_In~n float   %9.0g                 Myocardial Infarction
Peripheral_Ar~e float   %9.0g                 Peripheral Arterial Disease
Porcelain_Aorta float   %9.0g                 Porcelain Aorta
Transient_Isc~_ float   %9.0g                 Transient Ischemic Attack (TIA)
      Last edited by Nick Cox; 18 Jul 2023, 00:45.
      • 1 like

      Comment

      • #4
        wow, this is EXACTLY what I wanted. great code. I spent hours yesterday evening looking at stacking vs reshape syntax but just couldn't get it to work. thank you both for your help.

        -Waseem
        Last edited by Waseem Lutfi; 18 Jul 2023, 00:54.
        • 1 like

        Comment

        • #5
          Nick Cox just a small question.

          Do you think the following line of code:
          Code:
           
 gen occurrence = conditionhxoccurence == "Yes"
          should be:
          Code:
           
 gen occurrence = conditionhxoccurence == "Yes" if !missing(conditionhxoccurence)

          Comment

          • #6
            Fahad Mirza #4 I did wonder about that.

            @Waseem Lufti is telling us that the occurrences are reported as Yes and No but I agree with you: if there were empty strings then they should be coded as missings.

            Missing values appear after the reshape wide because of absent data, a different thing!

            The fine structure of the data example is important:

            . tab conditionhx

            conditionhx | Freq. Percent Cum.
            --------------------------------+-----------------------------------
            Atrial Fibrillation | 10 5.56 5.56
            Atrial Flutter | 10 5.56 11.11
            Cardiomyopathy | 2 1.11 12.22
            Carotid Artery Stenosis | 10 5.56 17.78
            Cerebrovascular Accident | 10 5.56 23.33
            Cerebrovascular Disease | 10 5.56 28.89
            Chronic Lung Disease | 10 5.56 34.44
            Conduction Defect | 8 4.44 38.89
            Dementia - Moderate to Severe | 10 5.56 44.44
            Diabetes Mellitus | 10 5.56 50.00
            Endocarditis | 10 5.56 55.56
            Heart Failure | 10 5.56 61.11
            Hostile Chest | 10 5.56 66.67
            Hypertension | 10 5.56 72.22
            Liver Disease | 10 5.56 77.78
            Myocardial Infarction | 10 5.56 83.33
            Peripheral Arterial Disease | 10 5.56 88.89
            Porcelain Aorta | 10 5.56 94.44
            Transient Ischemic Attack (TIA) | 10 5.56 100.00
            --------------------------------+-----------------------------------
            Total | 180 100.00

            • 1 like

            Comment

            • #7
              That makes sense

              Comment

              Previous Next
              Working...
              X