Stata String Range Cleaning

2
votes

I am cleaning a string variable in Stata that has numeric values but occasionally has values formatted as a range, as in 1-50 or 1-3, etc.

When I try to destring these variables, these pesky ranges prevent me from doing so.

What I would like to do is replace the range with the average of the first number and the last number in the range. I have tried the following string functions to do this: 

replace `var' = ((regexs(1) + regexs(3))/2) if regexm(`var', "([0-9]*)([\-])([0-9]*)")

However, Stata cannot understand the average ((regexs(1) + regexs(3))/2) because it reads regexs(1) and regexs(2) as substrings.

I know I could do this by creating new variables, but the data I am working with has thousands of variables, so I would really prefer to just replace the existing string.

Any ideas on how to do this?

Thanks in advance

3
stata
Note sometimes for ranges a better guess is the geometric mean, so long as both values are positive. (Thousands of variables or thousands of values?) - Nick Cox
Noted, thanks! The numbers may sometimes be negative and I have about 1500 variables, not values - Brian Goggin

3 Answers

2
votes

Here's one way which splits the strings into 2 where applicable and destrings them:

#delimit;
clear;

set obs 4;

input str4 x str4 y;
"13" "4-7";
"1-50" "7";
"1-3"  "9-20";
""     "4";

foreach var of varlist x y {;
    split `var', parse("-") destring;
    egen clean_`var' = rowmean(`var'1 `var'2);
    drop `var'1 `var'2;
};
2
votes

You can use real() to convert the strings to numeric. 

clear
set more off

input ///
str6 range
"1-50"
"1-3"
end

list

gen range2 = (real(regexs(1)) + real(regexs(3)))/2 if ///
    regexm(range, "([0-9]*)([\-])([0-9]*)")

list

However, if regular expressions were not strictly needed (something I can't be sure of) I would go with split, as Dimitriy already pointed out. Notice I generated a new variable. Getting rid of the original, is in general, not good practice.

1
votes

Another way to do it would be (starting with a string variable mystring) 

gen myst = subinstr(mystring, "-", " ", .) 
assert inlist(wordcount(myst), 1, 2) 
gen mynumeric = cond(word(myst, 2) == "", real(word(myst, 1)), (real(word(myst,1)) + real(word(myst, 2))/2)

This kind of method is pedestrian to those fluent in regular expression syntax but can be easier to think through for others.