Two ID variables for each row

[Saulius Ringaila]

Hey all,
In advance would like to say sorry for experienced users for asking this (just cannot find the answer). I have a dataset which is Millenium cohort study (MSC6). Physical Activity (PA) is recorded for 2 days (weekday and weekend), hence two rows for each ID variable, so the question is what is the best way to handle such data? I have reshaped it to wide (it was originally in long format) and made 2 variables instead of 1. So, now I have a set of variables for day 1 and set for day 2, however there are lot of missing values and I simply cannot drop them as the whole row drops. (Picture attached). This accelerometer data: MCID - Id variable; FCACCAD - day accelerometer was assigned. Apologies if this is not really clear and any help would be highly appreciated.
[CODE]
gen day2 = 2 if (FCACCAD==2)
replace day2 = 1 if (FCACCAD ==1)
gen day1 =1 if (FCACCAD ==1)
replace day1=2 if (FCACCAD ==2)

gen MCSPID = _n

reshape wide MCSID FCACCWEEKDAY FCACC_N_VALID_HRS FCACC_MEAN_ACC_24H FCACC_MVPA_MEAN_ACC_E1MIN_100MG FCACC_MVPA_E5S_B1M80_T100_ENMO, i(MCSPID) j(FCACCAD)

clonevar fca1 = FCACCWEEKDAY1
clonevar fca2 = FCACCWEEKDAY2
clonevar valid_hrs1 = FCACC_N_VALID_HRS1
clonevar valid_hrs2 = FCACC_N_VALID_HRS2
clonevar mean1 = FCACC_MEAN_ACC_24H1
clonevar mean2 = FCACC_MEAN_ACC_24H2
clonevar mvpa_mean1 = FCACC_MVPA_MEAN_ACC_E1MIN_100MG1
clonevar mvpa_mean2 = FCACC_MVPA_MEAN_ACC_E1MIN_100MG2
clonevar mvpa801 = FCACC_MVPA_E5S_B1M80_T100_ENMO1
clonevar mvpa802 = FCACC_MVPA_E5S_B1M80_T100_ENMO2

[CODE]

[Saulius Ringaila]

Or this..

[Saulius Ringaila]

Any help appreciated, and if I have to pay someone for a private tuition, it is not an issue. Help very much needed.

[Joro Kolev]

Nobody engages probably because you do not explain what you want to happen, what is the problem what you want to resolve, and what is the outcome you want to achieve, and you do you provide a datasample with -dataex-.

[Saulius Ringaila]

Nobody engages probably because you do not explain what you want to happen, what is the problem what you want to resolve, and what is the outcome you want to achieve, and you do you provide a datasample with -dataex-.

Thank you for the insight I want to have one row for every ID, the problem is, I have two rows for each ID. Ill try to provide it through dataex.

[Saulius Ringaila]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 MCSID byte(FCNUM00 FCACCAD FCACCWEEKDAY) float FCACC_N_VALID_HRS double FCACC_MEAN_ACC_24H int FCACC_MVPA_MEAN_ACC_E1MIN_100MG double FCACC_MVPA_E5S_B1M80_T100_ENMO
"M10015U" 1 1 2    24 34.95 152 86.92
"M10015U" 1 2 7    24 28.61 120 77.08
"M10016V" 1 1 1    24 35.15  97 27.92
"M10016V" 1 2 3    24  45.4 139 90.42
"M10018X" 1 1 7 18.25  30.3 107 38.92
end
label values FCNUM00 FCNUM00
label def FCNUM00 1 "1st Cohort Member of the family", modify
label values FCACCWEEKDAY FCACCWEEKDAY
label def FCACCWEEKDAY 1 "Sunday", modify
label def FCACCWEEKDAY 2 "Monday", modify
label def FCACCWEEKDAY 3 "Tuesday", modify
label def FCACCWEEKDAY 7 "Saturday", modify

[Saulius Ringaila]

Ok here we go, so I'll try to explain the issue. First of all variables: MCSID - id variable; FCNUM00 - member within the family; FCACCAD - order of day for accelerometer;FCACC_N_VALID_HRS - valid hours recorded by accelerometer;FCACC_MEAN_ACC_24H - mean;FCACC_MVPA_MEAN_ACC_E1MIN_100MG - total minutes in MVPA (moderate to vigorous physical activity);FCACC_MVPA_E5S_B1M80_T100_ENMO -Mins in mod/vig: 5sec epoch, 80% bout criteria 100 ENMO 1min.
I will be merging this dataset with other datasets for the same project, with BMI measures, income and etc. however I can only run multiple regression analyses with one row per ID, so the question is how do I go about this? I tried to reshape it to wide format, however am still left with 2 IDs per row. Thank you in advance.