Creating variables in long format and filling in empty rows by subject

[Jake Magel]

Hello,

I am trying to create a variable that identifies participants for whom the variable lx_item = "STAGE 1 (CHECK ONE) and for lx_item_value = "N/A" for the same row AND for whom the variable lx_item = "STAGE II (CHECK ALL THAT APPLY)" and for lx_item_value = "N/A" for the same row (see below n1 = 54 and 55). I would like the the new variable to indicate on each row for a given participant (enc_id) that they meet (1) the above criteria or do not meet the criteria (0).

I think I can take care of it in 3 stages by executing the code below then rewriting the code for for the STAGE II part above -- followed by combining the variables to indicate which participants meet both criteria-- but is there an easier way?

I appreciate the help. Please let me know if more clarification is needed.

capture drop nostage_temp
capture drop nostage
gen nostage_temp = 0 // set all rows to 0
replace nostage_temp = 1 if lx_item == "STAGE 1 (CHECK ONE)" & lx_item_value == "N/A"
// replace with 1 on row it occurs
bysort enc_id: egen nostage=max(nostage_temp)

* Example generated by -dataex-. For more info, type help dataex
clear
input float n1 long enc_id int lx_item_id_rc str35 lx_item strL lx_item_value float dos_rc str19 dos str30 visit_type long(visit_type_rc visit_type_rc1)
 1 275670060  161 "LUMBAR SYMPTOMS"                 "Pain"                               20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 2 275670060  162 "BUTTOCK SYMPTOMS"                "Pain"                               20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 3 275670060  163 "THIGH SYMPTOMS"                  "Pain"                               20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 4 275670060  164 "LUMBAR LOCATION"                 "Bilat"                              20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 5 275670060  165 "BUTTOCK LOCATION"                "Right"                              20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 6 275670060  166 "THIGH LOCATION"                  "Right"                              20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 7 275670060  167 "LOWER LEG LOCATION"              "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 8 275670060  168 "LOWER LEG SYMPTOMS"              "None"                               20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 9 275670060  170 "WORST ORDERING OF SYMPTOMS"      "Indeterminate"                      20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
10 275670060  171 "ORDERING OF SYMPTOMS BEST"       "Indeterminate"                      20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
11 275670060  172 "TEMPORAL WORST"                  "Indeterminate"                      20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
12 275670060  173 "TEMPORAL BEST"                   "Indeterminate"                      20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
13 275670060  175 "NATURE OF SYMPTOMS LUMBAR"       "Constant"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
14 275670060  176 "DURATION IN DAYS LUMBAR"         "less than or equal to 15 days"      20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
15 275670060  178 "SOUGHT CARE IN PAST LUMBAR"      "Yes"                                20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
16 275670060  179 "FIRST EPISODE"                   "years"                              20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
17 275670060  180 "PREVIOUS EPISODES"               ">5"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
18 275670060  182 "PREVIOUS CARE - EFFECTIVE"       "injections"                         20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
19 275670060  184 "FREQUENCY INCREASING LUMBAR"     "No"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
20 275670060  188 "POSTURE LUMBAR"                  ""                                   20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
21 275670060  215 "STANDING FLEXION TEST"           "Negative"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
22 275670060  216 "FLEXION"                         "Status Quo"                         20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
23 275670060  217 "EXTENSION"                       "Status Quo"                         20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
24 275670060  218 "RIGHT SIDE-BEND"                 "Status Quo"                         20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
25 275670060  219 "LEFT SIDE BEND"                  "Status Quo"                         20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
26 275670060  224 "DIRECTIONAL PREFERENCE"          "No Directional Preference"          20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
27 275670060  225 "ABERRANT MOVEMENT:"              "Yes"                                20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
28 275670060  226 "ABERRANT MOVEMENT 2"             "Gower's Sign"                       20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
29 275670060  226 "ABERRANT MOVEMENT 2"             "Painful Arc Returning from Flexion" 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
30 275670060  248 "L HIP FLEXION"                   "4"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
31 275670060  249 "L HIP ABDUCTION"                 "4+"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
32 275670060  250 "L HIP EXTENSION"                 "4"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
33 275670060  251 "L KNEE EXTENSION"                "4"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
34 275670060  252 "L ANT TIBIALIS"                  "5"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
35 275670060  255 "R HIP FLEXION"                   "4-"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
36 275670060  256 "R HIP ABDUCTION"                 "4+"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
37 275670060  257 "R HIP EXTENSION"                 "4"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
38 275670060  258 "R KNEE EXTENSION"                "4"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
39 275670060  259 "R ANT TIBIALIS"                  "5"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
40 275670060  392 "ROM: LEFT"                       "80"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
41 275670060  393 "ROM: RIGHT"                      "70"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
42 275670060  394 "AVERAGE SLR"                     "<90°"                              20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
43 275670060  395 "SCIATIC LEFT"                    "Negative"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
44 275670060  396 "SCIATIC RIGHT"                   "Negative"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
45 275670060  397 "LONG SIT TEST"                   "Negative"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
46 275670060  405 "LEFT FABER TEST"                 "Negative"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
47 275670060  406 "RIGHT FABER TEST"                "Negative"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
48 275670060  410 "MOBILITY L1"                     "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
49 275670060  411 "MOBILITY L2"                     "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
50 275670060  412 "MOBILITY L3"                     "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
51 275670060  413 "MOBILITY L4"                     "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
52 275670060  414 "MOBILITY L5"                     "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
53 275670060  423 "PRONE INSTABILITY TESTING:"      "NT"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
54 275670060  428 "STAGE 1 (CHECK ONE)"             "N/A"                                20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
55 275670060  429 "STAGE II (CHECK ALL THAT APPLY)" "N/A"                                20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
56 275670060  430 "STARTBACK STATUS (CHECK ONE)"    "Low Risk"                           20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
57 275670060  912 "RIGHT SIDE BENDING (RSB)"        "10"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
58 275670060  914 "LUMBAR EXTENSION (EXT)"          "5"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
59 275670060  915 "LUMBAR FLEXION (FLEX)"           "40"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
60 275670060  916 "LEFT SIDE BENDING (LSB)"         "10"                                 20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
61 275670060 3367 "INTERVENTIONS INITIAL & WEEKLY"  "Patient Education/Instruction"      20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
62 275670060 3370 "L GASTROC/SOLEUS"                "5"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
63 275670060 3371 "R GASTROC/SOLEUS"                "5"                                  20940 "5/1/2017 10:00 AM" "INITIAL PT " 7 1
 1 275734608  161 "LUMBAR SYMPTOMS"                 "Pain"                               20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 2 275734608  162 "BUTTOCK SYMPTOMS"                "Pain"                               20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 3 275734608  163 "THIGH SYMPTOMS"                  "Pain"                               20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 4 275734608  164 "LUMBAR LOCATION"                 "Bilat"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 5 275734608  165 "BUTTOCK LOCATION"                "Right"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 6 275734608  166 "THIGH LOCATION"                  "Right"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 7 275734608  167 "LOWER LEG LOCATION"              "Bilat"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 8 275734608  168 "LOWER LEG SYMPTOMS"              "Paresthesia"                        20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
 9 275734608  170 "WORST ORDERING OF SYMPTOMS"      "Sitting"                            20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
10 275734608  171 "ORDERING OF SYMPTOMS BEST"       "Walking"                            20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
11 275734608  172 "TEMPORAL WORST"                  "Indeterminate"                      20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
12 275734608  173 "TEMPORAL BEST"                   "Indeterminate"                      20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
13 275734608  175 "NATURE OF SYMPTOMS LUMBAR"       "Intermittent"                       20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
14 275734608  176 "DURATION IN DAYS LUMBAR"         "greater than 15 days"               20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
15 275734608  178 "SOUGHT CARE IN PAST LUMBAR"      "No"                                 20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
16 275734608  179 "FIRST EPISODE"                   "ongoing issue"                      20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
17 275734608  180 "PREVIOUS EPISODES"               "0"                                  20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
18 275734608  182 "PREVIOUS CARE - EFFECTIVE"       "exercises"                          20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
19 275734608  184 "FREQUENCY INCREASING LUMBAR"     "No"                                 20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
20 275734608  188 "POSTURE LUMBAR"                  "Lordosis"                           20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
21 275734608  207 "FRONT PELVIC"                    "Level"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
22 275734608  208 "BACK PELVIC"                     "Level"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
23 275734608  209 "PSIS PELVIC"                     "Level"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
24 275734608  210 "ASIS PELVIC"                     "Level"                              20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
25 275734608  215 "STANDING FLEXION TEST"           "Negative"                           20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
26 275734608  216 "FLEXION"                         "Status Quo"                         20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
27 275734608  217 "EXTENSION"                       "Status Quo"                         20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
28 275734608  218 "RIGHT SIDE-BEND"                 "Status Quo"                         20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
29 275734608  219 "LEFT SIDE BEND"                  "Status Quo"                         20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
30 275734608  224 "DIRECTIONAL PREFERENCE"          "No Directional Preference"          20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
31 275734608  225 "ABERRANT MOVEMENT:"              "No"                                 20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
32 275734608  228 "SEATED FLEXION TESTS"            "Negative"                           20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
33 275734608  229 "REPEATED FLEXION:"               "Status Quo"                         20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
34 275734608  231 "KNEE JERK"                       "2"                                  20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
35 275734608  232 "RIGHT KNEE JERK"                 "1"                                  20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
36 275734608  233 "LEFT ANKLE JERK"                 "2"                                  20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
37 275734608  234 "RIGHT ANKLE JERK"                "1"                                  20941 "5/2/2017 7:30 AM"  "INITIAL PT " 7 1
end
format %td dos_rc
label values visit_type_rc visit_type_rc
label def visit_type_rc 7 "INITIAL PT", modify
label values visit_type_rc1 visit_type_rc1
label def visit_type_rc1 1 "Initial PT", modify

[Clyde Schechter]

Your code works. It can be simplified a bit by replacing

gen nostage_temp = 0 // set all rows to 0
replace nostage_temp = 1 if lx_item == "STAGE 1 (CHECK ONE)" & lx_item_value == "N/A"

with the single command

gen nostage_temp =  (lx_item == "STAGE 1 (CHECK ONE)" & lx_item_value == "N/A")

And, actually, you don't really need to fully repeat all the code separately for the stage 2 criterion. The whole thing can be done in a single line with:

by enc_id, sort: egen nostage = max(inlist(lx_item, "STAGE 1 (CHECK ONE)", "STAGE II (CHECK ALL THAT APPLY)") & lx_item_value == "N/A")

[Jake Magel]

Thank you Clyde,

I am also running into the situation where for some participants under the lx_tiem variable the STAGE 1 (CHECK ONE)" and/or "STAGE II (CHECK ALL THAT APPLY)" rows don't exist. Do you have a suggestion how to flag the participants for whom one or both are not represented? -- i.e, not missing -- just not represented at all for some.

[Clyde Schechter]

I'm not sure I fully understand what you want in #3. The following code will identify any enc_id for which neither STAGE I (CHECK ONE) nor STAGE II (CHECK ALL THAT APPLY) appears as a value of lx_item in any of its associated observations.:

by enc_id, sort: egen no_stage_I_or_II = min(!inlist(lx_item, "STAGE 1 (CHECK ONE)", ///
    "STAGE II (CHECK ALL THAT APPLY)"))

Is that what you meant?

[Jake Magel]

Sorry for not being clear. Actually the row that contains STAGE 1 (CHECK ONE)" and/or the row containing "STAGE II (CHECK ALL THAT APPLY" does not exist. I am trying to identify individuals for whom 1 or both rows don't exist in the data.

Is there a way to add to:
by enc_id, sort: egen nostage = max(inlist(lx_item, "STAGE 1 (CHECK ONE)", "STAGE II (CHECK ALL THAT APPLY)") & lx_item_value == "N/A") to also identify participants for whom the entire row of data that should have "STAGE 1 (CHECK ONE)" or "STAGE II (CHECK ALL THAT APPLY) is absent?

[Clyde Schechter]

Well, in principle, yes, there is a way to combine the two. But it's pretty contorted, and opaque. I think it is better to do the two separately and then disjoin them.

by enc_id, sort: egen stage_na = max(inlist(lx_item, "STAGE 1 (CHECK ONE)", "STAGE II (CHECK ALL THAT APPLY)") & lx_item_value == "N/A")
by enc_id, sort: egen both_stages_absent = min(!inlist(lx_item, "STAGE 1 (CHECK ONE)", "STAGE II (CHECK ALL THAT APPLY)"))
gen byte stage_problem = stage_na | both_stages_absent

[Clyde Schechter]

I just realized you said you want to identify people who are missing either stage I or stage II, that is anybody who doesn't have both. So that's slightly different logic:

by enc_id, sort: egen stage_na = max(inlist(lx_item, "STAGE 1 (CHECK ONE)", "STAGE II (CHECK ALL THAT APPLY)") & lx_item_value == "N/A")
by enc_id: egen has_stage_1 = max(lx_item == "STAGE 1 (CHECK ONE)")
by enc_id: egen has_stage_2 = max(lx_item == "STAGE 2 (CHECK ALL THAT APPLY)")
gen byte stage_problem = stage_na | !(has_stage_1 & has_stage_2)