*! rensheet v1.0 26 January 2008 *! program to edit variable names and labels in a spreadsheet *! by Austin Nichols austinnichols@gmail.com prog rensheet version 8.2 syntax [varlist] using/ [, replace Append Label Do Run Val(varlist) Immed] if `"`using'`immed'"'=="" { di as err "Must specify using filename or immed option" error 198 } if `"`using'"'=="" { tempfile using loc run="run" } foreach v of local val { if substr("`:type `v''",1,3)=="str" { di as err "Variable `v' is not numeric: no value labels possible" loc val: list val - v } } foreach v of local val { loc lbl: value label `v' loc lbls `lbls' `lbl' } qui if "`val'"!="" { preserve tempfile lvals uselabel `lbls', clear save `lvals' restore } qui { if "`replace'"=="" & "`append'"=="" conf new file "`using'" preserve tempvar o l vl g `o'="" g `l'="" g `vl'="" loc n: word count `varlist' cap set obs `n' loc i=1 foreach v of local varlist { replace `l'="`: var label `v''" in `i' replace `vl'="`: value label `v''" in `i' replace `o'="`v'" in `i++' } keep `o' `l' `vl' keep if !mi(`o') ren `o' oldname g name=old ren `l' oldlabel g label=oldl ren `vl' lname if "`label'"=="" edit name else edit name label } tempname f file open `f' using "`using'", write `replace' `append' cap assert oldn==name & oldl==label if _rc!=0 { g obs=_n sort name cap assert name!=name[_n-1] if _rc { replace obs=_n if name==name[_n-1] su obs, meanonly di as err "Cannot use name `=name[`r(min)']' more than once" error 198 } sort obs forv i=1/`=_N' { if oldn[`i']!=name[`i'] { file write `f' "ren `=oldn[`i']' `=name[`i']'" _n } if oldl[`i']!=label[`i'] { file write `f' `"la var `=name[`i']' "`=label[`i']'""' _n } } } file close `f' tempfile lab if "`val'"!="" { drop label oldlab sort lname merge lname using `lvals' drop _m sort lname value qui save `lab' } restore qui if "`val'"!="" { foreach v of loc val { preserve keep `v' bys `v': g count=_N by `v': drop if _n>1 g lname="`: value label `v''" ren `v' value sort lname value merge lname value using `lab', nokeep replace count=0 if mi(count) g `v'=value ren label oldlabel g label=oldlab g c=count edit count `v' label cap assert c==count local r= _rc if `r'!=0 { di as err "Attempt to change counts! Variable `v' skipped" } cap assert value==`v' local r=max(_rc,`r') if _rc!=0 { di as err "Attempt to change values! Variable `v' skipped" } cap assert oldlab==label if _rc!=0 & `r'==0 { tempname f file open `f' using "`using'", write append file write `f' "la val `=name' `=name'" _n forv i=1/`=_N' { if label[`i']!="" { file write `f' `"la def `=name[`i']' `=value[`i']' "`=label[`i']'", modify"' _n } } file close `f' } restore } } if "`do'"!="" & "`run'"=="" do `using' if "`run'"!="" run `using' end