I have a dataset which consists of one column of variable names (varname) and one column of their labels (vlabel). I am trying to reshape this such that it consists of as many columns as there are variable names, with each variable name having its label below it. Then, I will iterate over the list of varnames and apply the vlabel to each. However, before I apply the label, I want to cut out any occurrences of double quotes in the vlabel to ensure that no errors arise, and I want to reduce the size of the label by eliminating occurrences of "Estimate"; and "Margin of Error", and replacing them with nothing and "MoE" respectively.
However, I am running into an error r(198). The code, error output, and data example are listed below:
With set trace on enabled, I hit the loop and obtain this output/error:
. foreach var of varlist _all {
2. local tempvar1 = subinstr(`var'[1], `"""', `""', .)
3. local tempvar2 = subinstr("`tempvar1'","Estimate;", .)
4. local tempvar3 = subinstr("`tempvar2'","Margin of Error;", "MoE;")
5. label variable `var' "`tempvar3'"
6. }
- foreach var of varlist _all {
- local tempvar1 = subinstr(`var'[1], `"""', `""', .)
= local tempvar1 = subinstr(cdp02_1est[1], `"""', `""', .)
- local tempvar2 = subinstr("`tempvar1'","Estimate;", .)
= local tempvar2 = subinstr("Number; Estimate; HOUSEHOLDS BY TYPE; Total households","Estimate;", .)
invalid syntax
local tempvar3 = subinstr("`tempvar2'","Margin of Error;", "MoE;")
label variable `var' "`tempvar3'"
}
Dataex included below:
Thank you for your help.
However, I am running into an error r(198). The code, error output, and data example are listed below:
Code:
clear all
cap log close
cap program drop _all
set maxvar 5000
ssc install sxpose
import excel ${local_publicschoolchildren_data}ACS-ED_2015-2019_RecordLayouts.xlsx, sheet("CDP_ChildPop") firstrow allstring clear
replace varname = lower(varname)
drop denominator
sxpose, clear firstnames
foreach var of varlist _all {
local tempvar1 = subinstr(`var'[1], `"""', `""', .)
local tempvar2 = subinstr("`tempvar1'","Estimate;", .)
local tempvar3 = subinstr("`tempvar2'","Margin of Error;", "MoE;")
label variable `var' "`tempvar3'"
}
. foreach var of varlist _all {
2. local tempvar1 = subinstr(`var'[1], `"""', `""', .)
3. local tempvar2 = subinstr("`tempvar1'","Estimate;", .)
4. local tempvar3 = subinstr("`tempvar2'","Margin of Error;", "MoE;")
5. label variable `var' "`tempvar3'"
6. }
- foreach var of varlist _all {
- local tempvar1 = subinstr(`var'[1], `"""', `""', .)
= local tempvar1 = subinstr(cdp02_1est[1], `"""', `""', .)
- local tempvar2 = subinstr("`tempvar1'","Estimate;", .)
= local tempvar2 = subinstr("Number; Estimate; HOUSEHOLDS BY TYPE; Total households","Estimate;", .)
invalid syntax
local tempvar3 = subinstr("`tempvar2'","Margin of Error;", "MoE;")
label variable `var' "`tempvar3'"
}
Dataex included below:
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str54 cdp02_1est str61 cdp02_1moe str77 cdp02_2est "Number; Estimate; HOUSEHOLDS BY TYPE; Total households" "Number; Margin of Error; HOUSEHOLDS BY TYPE; Total households" "Number; Estimate; HOUSEHOLDS BY TYPE; Total households; Married-couple family" end
Comment