Replace "Null"l by "" in a large dataset

[Liang Wang Jack]

Dear all,

I have 1100 variables in my dataset. How can I replace "Null" by " " in the string variables and replace " " by "0" in the numeric variables, by a loop? I had tried but get the error code.

. foreach v of var id-cortexvol {
  2. 
.         replace `v' = "" if `v' == "Null"
  3.         replace `v' = "0" if `v' == " " 
  4.         }
type mismatch
r(109);

    *

Here is the sample of the dataset.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id str4(gen edu) float mmse str11(lhcortexvol rhcortexvol) float tot str11 cortexvol
1 "F"    "1"    .5 "154802.6118" "142085.1472" .26 "296887.759" 
2 "M"    "2"    .9 "Null"        "Null"        .29 "Null"       
3 "F"    "1"    .9 "Null"        "Null"        .26 "Null"       
4 "M"    "Null"  . "190245.4856" "193321.063"    . "383566.5485"
5 "F"    "1"    .9 "Null"        "Null"        .26 "Null"       
6 "F"    "Null"  . "Null"        "Null"        .45 "Null"       
7 "Null" "1"    .9 "185859.7282" "192463.5706" .26 "378323.2988"
8 "F"    "5"     . "Null"        "Null"        .38 "Null"       
9 "F"    "1"    .9 "Null"        "Null"        .26 "Null"       
end

Best,
Jack Liang

[Nick Cox]

You need to loop over string variables only. Or capture errors. The first is easy:

ds id-cortexvol, has(type string) 

foreach v in `r(varlist)' {
    replace `v' = "" if `v' == "Null"
    replace `v' = "0" if `v' == " " 
}

[Liang Wang Jack]

You need to loop over string variables only. Or capture errors. The first is easy:

ds id-cortexvol, has(type string)

foreach v in `r(varlist)' {
replace `v' = "" if `v' == "Null"
replace `v' = "0" if `v' == " "
}

Thank you so much!

[Clyde Schechter]

You're getting that error because you can't try to replace `v' with "" or "0" or "anything" when `v' is not a string variable. And mmse and tot are not string variables. So you need to restrict this loop to just string variables:

ds, has(type string)
local string_vars `r(varlist)'
foreach v of varlist `string_vars' {
    replace `v' = "" if `v' == "Null"
    replace `v' = "0" if `v' ==" "
}

Unsolicited advice: rename your variable gen. -gen- is the legally and commonly abbreviated name of a Stata command, and using it as a variable name might lead to confusion later on. And conceivably in a sufficiently complicated situation it might even confuse the parser and lead to errors. Since its values are mostly M or F, I'm inferring that it represents gender or sex, so use one of those as the name.

Added: Crossed with #2 and #3.

[Liang Wang Jack]

You're getting that error because you can't try to replace `v' with "" or "0" or "anything" when `v' is not a string variable. And mmse and tot are not string variables. So you need to restrict this loop to just string variables:

ds, has(type string)
local string_vars `r(varlist)'
foreach v of varlist `string_vars' {
replace `v' = "" if `v' == "Null"
replace `v' = "0" if `v' ==" "
}

Unsolicited advice: rename your variable gen. -gen- is the legally and commonly abbreviated name of a Stata command, and using it as a variable name might lead to confusion later on. And conceivably in a sufficiently complicated situation it might even confuse the parser and lead to errors. Since its values are mostly M or F, I'm inferring that it represents gender or sex, so use one of those as the name.

Added: Crossed with #2 and #3.

Thanks for your advice!
Best,
Jack Liang