I have wide data where ID is the unique identifier, and a series of variables branch1 - branch3
that I'm wanting to transform from wide. The variable labels from the wide data, need to then be applied as value labels I think, so I can keep the branch names.
clear
input id branch1 branch2 branch3
1 0 1 1
2 1 1 1
3 0 0 0
4 1 0 1
5 0 1 0
end
* Labels are already come with the data file, just creating some for example
label variable branch1 "Branch Name A"
label variable branch2 "Branch Name B"
label variable branch3 "Branch Name C"
* Reshape to long
reshape long branch, i(id) j(brn_name)
From here I would like the desired output to be:
id brn_name branch branch_value
1 1 0 Branch Name A
1 2 1 Branch Name B
1 3 1 Branch Name C
2 1 1 Branch Name A
2 2 1 Branch Name B
2 3 1 Branch Name C
3 1 0 Branch Name A
3 2 0 Branch Name B
3 3 0 Branch Name C
4 1 1 Branch Name A
4 2 0 Branch Name B
4 3 1 Branch Name C
5 1 0 Branch Name A
5 2 1 Branch Name B
5 3 0 Branch Name C
Since your wanted variable branch_value
is basically a label for the values of brn_name
, you could take advantage of Stata's capabilities to have a numeric variable with value labels.
The code below will do this if you don't execute the last two lines. Otherwise, execute the last two lines to get the result you ask for.
clear
input id branch1 branch2 branch3
1 0 1 1
2 1 1 1
3 0 0 0
4 1 0 1
5 0 1 0
end
* Labels are already come with the data file, just creating some for example
label variable branch1 "Branch Name A"
label variable branch2 "Branch Name B"
label variable branch3 "Branch Name C"
// Build value label
foreach var of varlist branch* {
local branch_nr: subinstr local var "branch" ""
label define branches_label `branch_nr' "`:variable label `var''", add
}
// Reshape
reshape long branch, i(id) j(brn_name)
// Assign label to brn_name
label values brn_name branches_label
// Possibly stop here!
// Decode value labels
decode brn_name, gen(branch_value)
label values brn_name .