Creating variables in long format

[Jake Magel]

Hello, I am trying to create a variable "stage1_class" that represents different responses to lx_item "STAGE 1 (CHECK ONE)" that are represented in lx_item_value as 1 of the following: "Extension Directional Preference","Flexion Directional Preference" ///
"Non-Thrust Mobilization (Grade IV)", "Stabilization", "Thrust Mobilization", "Traction", "NA".

I am trying to create this variable by enc_id so that all rows of enc_id for a subject contain the dummy code.

The code below is of course wrong but I am not sure where to go from here. I believe the correct code involves the use of _n-1 somehow to fill in the rows but I am stuck.....

capture drop stage1_class
bysort enc_id:gen stage1_class = 1 if lx_item_value == "Extension Directional Preference"
replace stage1_class = 2 if lx_item_value == "Flexion Directional Preference"
replace stage1_class = 3 if lx_item_value == "Non-Thrust Mobilization (Grade IV)"
replace stage1_class = 4 if lx_item_value == "Stabilization"
replace stage1_class = 5 if lx_item_value == "Thrust Mobilization"
replace stage1_class = 6 if lx_item_value == "Traction"
replace stage1_class = 7 if lx_item_value == "NA"

* Example generated by -dataex-. For more info, type help dataex
clear
input long enc_id int lx_item_id_rc str35 lx_item strL lx_item_value float(stage1_na stage1_absent stage2_na stage2_absent stage1and2_absent)
275670060  161 "LUMBAR SYMPTOMS"                 "Pain"                               1 0 1 0 0
275670060  162 "BUTTOCK SYMPTOMS"                "Pain"                               1 0 1 0 0
275670060  163 "THIGH SYMPTOMS"                  "Pain"                               1 0 1 0 0
275670060  164 "LUMBAR LOCATION"                 "Bilat"                              1 0 1 0 0
275670060  165 "BUTTOCK LOCATION"                "Right"                              1 0 1 0 0
275670060  166 "THIGH LOCATION"                  "Right"                              1 0 1 0 0
275670060  167 "LOWER LEG LOCATION"              "NT"                                 1 0 1 0 0
275670060  168 "LOWER LEG SYMPTOMS"              "None"                               1 0 1 0 0
275670060  170 "WORST ORDERING OF SYMPTOMS"      "Indeterminate"                      1 0 1 0 0
275670060  171 "ORDERING OF SYMPTOMS BEST"       "Indeterminate"                      1 0 1 0 0
275670060  172 "TEMPORAL WORST"                  "Indeterminate"                      1 0 1 0 0
275670060  173 "TEMPORAL BEST"                   "Indeterminate"                      1 0 1 0 0
275670060  175 "NATURE OF SYMPTOMS LUMBAR"       "Constant"                           1 0 1 0 0
275670060  176 "DURATION IN DAYS LUMBAR"         "less than or equal to 15 days"      1 0 1 0 0
275670060  178 "SOUGHT CARE IN PAST LUMBAR"      "Yes"                                1 0 1 0 0
275670060  179 "FIRST EPISODE"                   "years"                              1 0 1 0 0
275670060  180 "PREVIOUS EPISODES"               ">5"                                 1 0 1 0 0
275670060  182 "PREVIOUS CARE - EFFECTIVE"       "injections"                         1 0 1 0 0
275670060  184 "FREQUENCY INCREASING LUMBAR"     "No"                                 1 0 1 0 0
275670060  188 "POSTURE LUMBAR"                  ""                                   1 0 1 0 0
275670060  215 "STANDING FLEXION TEST"           "Negative"                           1 0 1 0 0
275670060  216 "FLEXION"                         "Status Quo"                         1 0 1 0 0
275670060  217 "EXTENSION"                       "Status Quo"                         1 0 1 0 0
275670060  218 "RIGHT SIDE-BEND"                 "Status Quo"                         1 0 1 0 0
275670060  219 "LEFT SIDE BEND"                  "Status Quo"                         1 0 1 0 0
275670060  224 "DIRECTIONAL PREFERENCE"          "No Directional Preference"          1 0 1 0 0
275670060  225 "ABERRANT MOVEMENT:"              "Yes"                                1 0 1 0 0
275670060  226 "ABERRANT MOVEMENT 2"             "Gower's Sign"                       1 0 1 0 0
275670060  226 "ABERRANT MOVEMENT 2"             "Painful Arc Returning from Flexion" 1 0 1 0 0
275670060  248 "L HIP FLEXION"                   "4"                                  1 0 1 0 0
275670060  249 "L HIP ABDUCTION"                 "4+"                                 1 0 1 0 0
275670060  250 "L HIP EXTENSION"                 "4"                                  1 0 1 0 0
275670060  251 "L KNEE EXTENSION"                "4"                                  1 0 1 0 0
275670060  252 "L ANT TIBIALIS"                  "5"                                  1 0 1 0 0
275670060  255 "R HIP FLEXION"                   "4-"                                 1 0 1 0 0
275670060  256 "R HIP ABDUCTION"                 "4+"                                 1 0 1 0 0
275670060  257 "R HIP EXTENSION"                 "4"                                  1 0 1 0 0
275670060  258 "R KNEE EXTENSION"                "4"                                  1 0 1 0 0
275670060  259 "R ANT TIBIALIS"                  "5"                                  1 0 1 0 0
275670060  392 "ROM: LEFT"                       "80"                                 1 0 1 0 0
275670060  393 "ROM: RIGHT"                      "70"                                 1 0 1 0 0
275670060  394 "AVERAGE SLR"                     "<90°"                              1 0 1 0 0
275670060  395 "SCIATIC LEFT"                    "Negative"                           1 0 1 0 0
275670060  396 "SCIATIC RIGHT"                   "Negative"                           1 0 1 0 0
275670060  397 "LONG SIT TEST"                   "Negative"                           1 0 1 0 0
275670060  405 "LEFT FABER TEST"                 "Negative"                           1 0 1 0 0
275670060  406 "RIGHT FABER TEST"                "Negative"                           1 0 1 0 0
275670060  410 "MOBILITY L1"                     "NT"                                 1 0 1 0 0
275670060  411 "MOBILITY L2"                     "NT"                                 1 0 1 0 0
275670060  412 "MOBILITY L3"                     "NT"                                 1 0 1 0 0
275670060  413 "MOBILITY L4"                     "NT"                                 1 0 1 0 0
275670060  414 "MOBILITY L5"                     "NT"                                 1 0 1 0 0
275670060  423 "PRONE INSTABILITY TESTING:"      "NT"                                 1 0 1 0 0
275670060  428 "STAGE 1 (CHECK ONE)"             "N/A"                                1 0 1 0 0
275670060  429 "STAGE II (CHECK ALL THAT APPLY)" "N/A"                                1 0 1 0 0
275670060  430 "STARTBACK STATUS (CHECK ONE)"    "Low Risk"                           1 0 1 0 0
275670060  912 "RIGHT SIDE BENDING (RSB)"        "10"                                 1 0 1 0 0
275670060  914 "LUMBAR EXTENSION (EXT)"          "5"                                  1 0 1 0 0
275670060  915 "LUMBAR FLEXION (FLEX)"           "40"                                 1 0 1 0 0
275670060  916 "LEFT SIDE BENDING (LSB)"         "10"                                 1 0 1 0 0
275670060 3367 "INTERVENTIONS INITIAL & WEEKLY"  "Patient Education/Instruction"      1 0 1 0 0
275670060 3370 "L GASTROC/SOLEUS"                "5"                                  1 0 1 0 0
275670060 3371 "R GASTROC/SOLEUS"                "5"                                  1 0 1 0 0
275734608  161 "LUMBAR SYMPTOMS"                 "Pain"                               1 0 0 0 0
275734608  162 "BUTTOCK SYMPTOMS"                "Pain"                               1 0 0 0 0
275734608  163 "THIGH SYMPTOMS"                  "Pain"                               1 0 0 0 0
275734608  164 "LUMBAR LOCATION"                 "Bilat"                              1 0 0 0 0
275734608  165 "BUTTOCK LOCATION"                "Right"                              1 0 0 0 0
275734608  166 "THIGH LOCATION"                  "Right"                              1 0 0 0 0
275734608  167 "LOWER LEG LOCATION"              "Bilat"                              1 0 0 0 0
275734608  168 "LOWER LEG SYMPTOMS"              "Paresthesia"                        1 0 0 0 0
275734608  170 "WORST ORDERING OF SYMPTOMS"      "Sitting"                            1 0 0 0 0
275734608  171 "ORDERING OF SYMPTOMS BEST"       "Walking"                            1 0 0 0 0
275734608  172 "TEMPORAL WORST"                  "Indeterminate"                      1 0 0 0 0
275734608  173 "TEMPORAL BEST"                   "Indeterminate"                      1 0 0 0 0
275734608  175 "NATURE OF SYMPTOMS LUMBAR"       "Intermittent"                       1 0 0 0 0
275734608  176 "DURATION IN DAYS LUMBAR"         "greater than 15 days"               1 0 0 0 0
275734608  178 "SOUGHT CARE IN PAST LUMBAR"      "No"                                 1 0 0 0 0
275734608  179 "FIRST EPISODE"                   "ongoing issue"                      1 0 0 0 0
275734608  180 "PREVIOUS EPISODES"               "0"                                  1 0 0 0 0
275734608  182 "PREVIOUS CARE - EFFECTIVE"       "exercises"                          1 0 0 0 0
275734608  184 "FREQUENCY INCREASING LUMBAR"     "No"                                 1 0 0 0 0
275734608  188 "POSTURE LUMBAR"                  "Lordosis"                           1 0 0 0 0
275734608  207 "FRONT PELVIC"                    "Level"                              1 0 0 0 0
275734608  208 "BACK PELVIC"                     "Level"                              1 0 0 0 0
275734608  209 "PSIS PELVIC"                     "Level"                              1 0 0 0 0
275734608  210 "ASIS PELVIC"                     "Level"                              1 0 0 0 0
275734608  215 "STANDING FLEXION TEST"           "Negative"                           1 0 0 0 0
275734608  216 "FLEXION"                         "Status Quo"                         1 0 0 0 0
275734608  217 "EXTENSION"                       "Status Quo"                         1 0 0 0 0
275734608  218 "RIGHT SIDE-BEND"                 "Status Quo"                         1 0 0 0 0
275734608  219 "LEFT SIDE BEND"                  "Status Quo"                         1 0 0 0 0
275734608  224 "DIRECTIONAL PREFERENCE"          "No Directional Preference"          1 0 0 0 0
275734608  225 "ABERRANT MOVEMENT:"              "No"                                 1 0 0 0 0
275734608  228 "SEATED FLEXION TESTS"            "Negative"                           1 0 0 0 0
275734608  229 "REPEATED FLEXION:"               "Status Quo"                         1 0 0 0 0
275734608  231 "KNEE JERK"                       "2"                                  1 0 0 0 0
275734608  232 "RIGHT KNEE JERK"                 "1"                                  1 0 0 0 0
275734608  233 "LEFT ANKLE JERK"                 "2"                                  1 0 0 0 0
275734608  234 "RIGHT ANKLE JERK"                "1"                                  1 0 0 0 0
end
label values stage1_na no_stage1lab
label values stage1_absent stage1_absentlab
label values stage2_na no_stage2lab
label values stage2_absent stage2_absentlab
label def stage2_absentlab 0 "0) stage 1 present", modify

[Jake Magel]

Also, for some of the subjects, there is no row that represents "STAGE 1 (CHECK ONE)" so they need to be identified also in some way

[Ali Atia]

clear
input str35 lx_item_value double stage1_class_temp
"Extension Directional Preference" 1
"Flexion Directional Preference" 2
"Non-Thrust Mobilization (Grade IV)" 3
"Stabilization" 4
"Thrust Mobilization" 5
"Traction" 6
"NA" 7
end
tempfile temp
save `temp'

use "your_dataset", clear

recast str lx_item_value 
merge m:1 lx_item_value using `temp', nogen keep(1 3)
bysort enc_id: egen stage1_class = max(stage1_class_temp)
replace stage1_class = 0 if stage1_class==.
drop stage1_class_temp

[Jake Magel]

Thank you Ali, I think this is almost correct. I have participants where the response to lx_item "STAGE 1 (CHECK ONE)" contained in lx_item_value is missing and some participants where the actual row that contains the "STAGE 1 (CHECK ONE)" is absent (does not exist) rather than missing. I see that the 0 indicates missing. How can I add another indicator for the participants where the "STAGE 1 (CHECK ONE)" is absent (does not exist)?

[Ali Atia]

Stata has a number of useful extended missing values which can employed to indicate different types of missingness. So you can do:

clear
input str35 lx_item_value double stage1_class_temp
"Extension Directional Preference" 1
"Flexion Directional Preference" 2
"Non-Thrust Mobilization (Grade IV)" 3
"Stabilization" 4
"Thrust Mobilization" 5
"Traction" 6
"NA" 7
end
tempfile temp
save `temp'

use "your_dataset", clear

recast str lx_item_value
merge m:1 lx_item_value using `temp', nogen keep(1 3)
bysort enc_id: egen stage1_class = max(stage1_class_temp)
bysort enc_id: egen missing = max(lx_item=="STAGE 1 (CHECK ONE)" & lx_item_value=="")
bysort enc_id: egen absent = min(lx_item!="STAGE 1 (CHECK ONE)")
replace stage1_class = .a if missing
replace stage1_class = .b if absent
drop stage1_class_temp

I use the extended missing value .a to indicate lx_item being STAGE 1 (CHECK ONE) and lx_item_value being missing, and the extended missing value .b to indicate lx_item never being STAGE 1 (CHECK ONE).

[Jake Magel]

This code worked beautifully, thank you.