Many-to-one mappings: reshaping to long for multiple variables

[Luis Mijares Castaneda]

Hello,

I have a dataset imported from a survey where certain questions allowed respondents to "choose all that apply." These responses were recorded as multiple variables. I want to reformat these variables into a more compact structure.

I came across the method suggested by Stata (https://www.stata.com/support/faqs/d...ple-responses/), but my dataset contains multiple sets of these variables. When I reshape one set, I encounter an issue where the identifier is no longer unique, preventing me from using the reshape command on other variables.

I attempted to create a unique identifier after the first reshape operation, which allowed the command to work, but the data was not formatted correctly.

Attached is a list of the variables I need to transform. Any guidance on the best way to structure this data properly would be greatly appreciated.

Thank you.

* Example generated by -dataex-. For more info, type help dataex
clear
input double(Caseid Q1_1 Q1_2 Q1_3 Q1_4 Q1_5 Q1_6 Q1_7 Q1_8 Q1_9 Q1_10) strL Q1_10_TEXT double(Q4_1 Q4_2 Q4_3 Q4_4 Q4_5 Q4_6 Q4_7 Q4_8 Q4_9 Q4_10 Q4_11 Q4_12)
265240001 -1 -1  1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240002 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240003 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1 -1 -1  1 -1 -1 -1 -1 -1
265240004 -1 -1 -1  1  1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240005 -1 -1 -1  1 -1  1 -1 -1  1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240006 -1 -1 -1 -1 -1 -1 -1 -1 -1  1 "MediCal"                        .  .  .  .  .  .  .  .  .  .  .  .
265240007 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240008 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240009 -1 -1 -1 -1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240010 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240011 -1  1 -1  1 -1  1 -1 -1 -1 -1 "-1"                            -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1 -1
265240012 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240013 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240014 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240015 -1 -1  1 -1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240016 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240017 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240018 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1 -1 -1 -1 -1 -1 -1 -1  1 -1
265240019 -1 -1 -1  1 -1 -1 -1 -1 -1  1 "United Healthcare supplement."  .  .  .  .  .  .  .  .  .  .  .  .
265240020 -1 -1 -1 -1  1 -1 -1 -1 -1 -1 "-1"                            -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240021 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240022 -1 -1 -1 -1 -1 -1 -1 -1 -1  1 "BCBS of Michigan"               .  .  .  .  .  .  .  .  .  .  .  .
265240023 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240024 -1 -1  1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240025 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240026 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240027 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240028 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240029 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240030 -1  1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240031 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1 -1
265240032 -1 -1  1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240033 -1 -1 -1 -1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240034 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240035 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240036 -1 -1 -1  1  1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240037 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240038 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240039 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1 -1
265240040 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240041 -1  1 -1 -1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240042 -1  1 -1 -1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240043 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240044 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240045 -1 -1  1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240046 -1 -1 -1  1 -1  1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240047 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1
265240048 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240049 -1  1 -1 -1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240050 -1  1 -1 -1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240051 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240052 -1 -1 -1  1 -1 -1 -1 -1 -1  1 "Medicare Advantage"             1 -1 -1 -1 -1 -1  1 -1 -1 -1 -1 -1
265240053 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240054 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240055 -1 -1 -1  1 -1  1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240056 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1 -1
265240057 -1 -1 -1  1  1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240058 -1 -1 -1  1 -1 -1 -1 -1 -1  1 "Medicare supplement"            .  .  .  .  .  .  .  .  .  .  .  .
265240059 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240060 -1  1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240061 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240062 -1 -1 -1  1 -1 -1 -1 -1 -1  1 "Medicare advantage"             1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240063 -1 -1 -1 -1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240064 -1 -1 -1 -1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240065 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240066 -1  1 -1 -1 -1  1 -1 -1  1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240067 -1 -1 -1 -1 -1  1 -1  1 -1 -1 "-1"                            -1  1 -1 -1 -1  1 -1 -1 -1 -1 -1 -1
265240068 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1
265240069  1 -1 -1 -1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1  1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240070 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240071 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240072 -1  1 -1 -1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240073 -1 -1 -1 -1 -1 -1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240074 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240075 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240076 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240077 -1 -1 -1  1 -1  1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240078 -1  1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240079 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240080 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240081 -1 -1  1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1 -1
265240082 -1 -1 -1  1 -1 -1 -1 -1  1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240083 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240084 -1  1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
265240085 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240086 -1  1  1 -1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1 -1 -1 -1 -1 -1 -1  1 -1 -1
265240087 -1 -1 -1 -1 -1 -1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240088 -1 -1 -1 -1 -1 -1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240089 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240090 -1 -1 -1  1 -1 -1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240091 -1 -1 -1 -1  1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240092 -1  1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240093 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 "-1"                            -1 -1 -1 -1  1 -1 -1 -1 -1 -1 -1 -1
265240094 -1 -1 -1 -1 -1 -1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240095 -1 -1 -1  1  1 -1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240096 -1 -1 -1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240097 -1 -1 -1 -1 -1 -1 -1 -1 -1  1 "Medicare Advantage"             .  .  .  .  .  .  .  .  .  .  .  .
265240098 -1 -1  1  1 -1  1 -1 -1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240099 -1 -1 -1  1 -1 -1 -1  1 -1 -1 "-1"                             .  .  .  .  .  .  .  .  .  .  .  .
265240100 -1 -1 -1  1 -1  1 -1 -1 -1  1 "BLCRS_BlSHLD"                   .  .  .  .  .  .  .  .  .  .  .  .
end
label values Q1_1 Q1_1
label def Q1_1 -1 "Skipped", modify
label def Q1_1 1 "No health insurance", modify
label values Q1_2 Q1_2
label def Q1_2 -1 "Skipped", modify
label def Q1_2 1 "Insurance through current or former employer", modify
label values Q1_3 Q1_3
label def Q1_3 -1 "Skipped", modify
label def Q1_3 1 "Insurance purchased directly from insurance company", modify
label values Q1_4 Q1_4
label def Q1_4 -1 "Skipped", modify
label def Q1_4 1 "Medicare", modify
label values Q1_5 Q1_5
label def Q1_5 -1 "Skipped", modify
label def Q1_5 1 "Medicaid", modify
label values Q1_6 Q1_6
label def Q1_6 -1 "Skipped", modify
label def Q1_6 1 "Department of Veterans Affairs (VA)", modify
label values Q1_7 Q1_7
label def Q1_7 -1 "Skipped", modify
label values Q1_8 Q1_8
label def Q1_8 -1 "Skipped", modify
label def Q1_8 1 "TRICARE, CHAMPUS, or other military health care", modify
label values Q1_9 Q1_9
label def Q1_9 -1 "Skipped", modify
label def Q1_9 1 "Indian Health Service", modify
label values Q1_10 Q1_10
label def Q1_10 -1 "Skipped", modify
label def Q1_10 1 "Other type of health insurance or coverage, please specify", modify
label values Q4_1 Q4_1
label def Q4_1 -1 "Skipped", modify
label def Q4_1 1 "Army", modify
label values Q4_2 Q4_2
label def Q4_2 -1 "Skipped", modify
label def Q4_2 1 "Air Force", modify
label values Q4_3 Q4_3
label def Q4_3 -1 "Skipped", modify
label def Q4_3 1 "Marine Corps", modify
label values Q4_4 Q4_4
label def Q4_4 -1 "Skipped", modify
label def Q4_4 1 "Navy", modify
label values Q4_5 Q4_5
label def Q4_5 -1 "Skipped", modify
label def Q4_5 1 "Army National Guard", modify
label values Q4_6 Q4_6
label def Q4_6 -1 "Skipped", modify
label def Q4_6 1 "Air National Guard", modify
label values Q4_7 Q4_7
label def Q4_7 -1 "Skipped", modify
label def Q4_7 1 "Army Reserve", modify
label values Q4_8 Q4_8
label def Q4_8 -1 "Skipped", modify
label values Q4_9 Q4_9
label def Q4_9 -1 "Skipped", modify
label values Q4_10 Q4_10
label def Q4_10 -1 "Skipped", modify
label def Q4_10 1 "Air Force Reserve", modify
label values Q4_11 Q4_11
label def Q4_11 -1 "Skipped", modify
label def Q4_11 1 "Coast Guard", modify
label values Q4_12 Q4_12
label def Q4_12 -1 "Skipped", modify

[Ken Chui]

And what is the end product you are envisioning? You said "make it more compact", what does that mean?

[Luis Mijares Castaneda]

I can format the first series of questions properly, but not the rest, so something like this

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input double Caseid str23 Health_Insurance double Q1_
265240001 "CHIP"                    0
265240001 "DirectPurchaseInsurance" 1
265240001 "EmployerInsurance"       0
265240001 "IndianHealthService"     0
265240001 "Medicaid"                0
265240001 "Medicare"                1
265240001 "MilitaryInsurance"       0
265240001 "NoHealthInsurance"       0
265240001 "OtherInsurance"          0
265240001 "VAInsurance"             1
265240002 "CHIP"                    0
265240002 "DirectPurchaseInsurance" 0
265240002 "EmployerInsurance"       1
265240002 "IndianHealthService"     0
265240002 "Medicaid"                0
265240002 "Medicare"                1
265240002 "MilitaryInsurance"       0
265240002 "NoHealthInsurance"       0
265240002 "OtherInsurance"          0
265240002 "VAInsurance"             0
265240003 "CHIP"                    0
265240003 "DirectPurchaseInsurance" 1
265240003 "EmployerInsurance"       0
265240003 "IndianHealthService"     0
265240003 "Medicaid"                0
265240003 "Medicare"                1
265240003 "MilitaryInsurance"       0
265240003 "NoHealthInsurance"       0
265240003 "OtherInsurance"          0
265240003 "VAInsurance"             0
265240004 "CHIP"                    0
265240004 "DirectPurchaseInsurance" 0
265240004 "EmployerInsurance"       0
265240004 "IndianHealthService"     0
265240004 "Medicaid"                1
265240004 "Medicare"                1
265240004 "MilitaryInsurance"       0
265240004 "NoHealthInsurance"       0
265240004 "OtherInsurance"          0
265240004 "VAInsurance"             1
265240005 "CHIP"                    0
265240005 "DirectPurchaseInsurance" 0
265240005 "EmployerInsurance"       0
265240005 "IndianHealthService"     1
265240005 "Medicaid"                0
265240005 "Medicare"                1
265240005 "MilitaryInsurance"       0
265240005 "NoHealthInsurance"       0
265240005 "OtherInsurance"          0
265240005 "VAInsurance"             1
265240006 "CHIP"                    0
265240006 "DirectPurchaseInsurance" 0
265240006 "EmployerInsurance"       0
265240006 "IndianHealthService"     0
265240006 "Medicaid"                0
265240006 "Medicare"                0
265240006 "MilitaryInsurance"       0
265240006 "NoHealthInsurance"       0
265240006 "OtherInsurance"          1
265240006 "VAInsurance"             0
265240007 "CHIP"                    0
265240007 "DirectPurchaseInsurance" 0
265240007 "EmployerInsurance"       0
265240007 "IndianHealthService"     0
265240007 "Medicaid"                0
265240007 "Medicare"                1
265240007 "MilitaryInsurance"       0
265240007 "NoHealthInsurance"       0
265240007 "OtherInsurance"          0
265240007 "VAInsurance"             0
265240008 "CHIP"                    0
265240008 "DirectPurchaseInsurance" 0
265240008 "EmployerInsurance"       0
265240008 "IndianHealthService"     0
265240008 "Medicaid"                0
265240008 "Medicare"                1
265240008 "MilitaryInsurance"       0
265240008 "NoHealthInsurance"       0
265240008 "OtherInsurance"          0
265240008 "VAInsurance"             1
265240009 "CHIP"                    0
265240009 "DirectPurchaseInsurance" 0
265240009 "EmployerInsurance"       0
265240009 "IndianHealthService"     0
265240009 "Medicaid"                0
265240009 "Medicare"                0
265240009 "MilitaryInsurance"       0
265240009 "NoHealthInsurance"       0
265240009 "OtherInsurance"          0
265240009 "VAInsurance"             1
265240010 "CHIP"                    0
265240010 "DirectPurchaseInsurance" 0
265240010 "EmployerInsurance"       0
265240010 "IndianHealthService"     0
265240010 "Medicaid"                0
265240010 "Medicare"                1
265240010 "MilitaryInsurance"       0
265240010 "NoHealthInsurance"       0
265240010 "OtherInsurance"          0
265240010 "VAInsurance"             0
end
label values Q1_ Q1_6

[Hemanshu Kumar]

I think you'll have to do it question-wise.

Something like this:

local questions Q1 Q4

preserve
    clear
    tempfile longform
    save `longform', emptyok
restore

foreach ques of local questions {
    preserve
        keep Caseid `ques'_*
        foreach var of varlist `ques'_* {
            capture confirm string var `var' 
            if _rc != 0 {
                decode `var', gen(_`var')
                drop `var'
                rename _`var' `var'
            }
        }
        reshape long `ques' , i(Caseid) j(ques_num) string
        replace ques_num = "`ques'" + ques_num
        rename `ques' answer
        append using `longform'
        save `longform', replace
    restore
}

use `longform', clear

[Luis Mijares Castaneda]

Is there a way to make each question it's own column? the way I did the dataex above, but a column for categories of Q1 answers and Q4 answers? I would like to structure the data similar to the 3.2 Many-to-one mappings: reshaping to long found in the suggestions here: https://www.stata.com/support/faqs/d...ple-responses/

[Hemanshu Kumar]

Perhaps something like this?

local questions Q1 Q4

foreach ques of local questions {
    preserve
        keep Caseid `ques'_*
        foreach var of varlist `ques'_* {
            capture confirm string var `var' 
            if _rc != 0 {
                decode `var', gen(_`var')
                drop `var'
                rename _`var' `var'
            }
        }
        reshape long `ques'_ , i(Caseid) j(opt_num) string
        rename `ques'_ `ques'
        if "`:word 1 of `questions''" == "`ques'" {
            tempfile longform
            save `longform'
        }
        else {
            merge 1:1 Caseid opt_num using `longform', nogen
            save `longform', replace
        }
    restore
}

use `longform', clear
format %12.0f Caseid
sort Caseid opt_num