Horribly dirty variable

Horribly dirty variable

by Juno -

Hi, I'm still trying to clean a super dirty variable with lots of text in it. 

The variable is pre-operative uterine size. Instead of just numbers though, there's lots of text sometimes associated with the numbers. I pulled out all the variations of text and came up with a list and wanted to apply it to the variable with the following command but...can't get it to work, any suggestions:

 

restring size_bimannual_h_p, gen (utsizehp) ignore (x)

 

where x is the paragraph of text below.

I have each phrase as it appears in the dataset with quotations around the text.

I wasn't sure a) how to make this work, clearly ;), b) if I should separate with parentheses or not

Any help is greatly appreciated!

 

x = 

("small" "bicornate v dedelphys" "difficult pelvic exam 2/2 pt discomfort" "wk size ut" "weeks" "slightly enlarged" "wks" "wk uterus" "large" "wk" "irregular" "small, retroverted" "normal" "mobile but wide," "~" "Bulky uterus, ant. displaced" "small mobile uterus" "wk size uterus" "10x6x6 cm uterus" "small RV" "'normal size'" "large uterine mass" "large uterine size" "wk size" "enlarged" "wk size" "bulky" "palpable abd mass to umbilicus centrally" "large" "cm size uterus, midline" "tiny" "week size uterus" "+" "HTN, depression, knee pain" "slightly enlarged" "wk ut" "large uterine mass (not distinct from uterus)" "small," "large, 7cm above umb." "fundus 11 weeks, bulk palpable on R to 15 weeks" "irregular (two upward arrows) size ut." "mobile wide uterus" "large," "obese" "mobile" "ut" "wks" "size" "cm" "very small" "globularly" "uterus" "to" "mobile," "cm, large out mass (?)" "bulbous" "myomatous" "small mobile uterus" "n due to guarding" "NSS uterus--lemon size" "DMT2, HTN, hypothyroid, s/p colon CA" "regular-shaped" "smalll" "difficult to assess due to obesity")

Re: Horribly dirty variable

by Michael Peters -

Juno,

  I think I am going to need more information to answer your question.  Do the numbers in your dirty variable always follow a specific phrase or are they just randomly distributed? How can you tell when you look at the variable for one observation what numbers are the uterus size?

I think the command you are going to want to use will be some deviation of regexs.  Please see the link below for some ways to use this command, and see if you can get it to do what you want. 

http://www.ats.ucla.edu/stat/stata/faq/regex.htm

 

MCP

Re: Horribly dirty variable

by Juno -

HI Michael, 

Thanks for getting me on the right track. The UCLA website is very helpful. The challenge with the way the data was entered into the dataset is that the text I sent before could be before or after the the actual uterine size and could be single or double digit and could be trying to express a range of values.

For example:

"large, 7cm above umb."  Where I would want to extract the 7.

 or

"15 wk size ut." Where I would want to extract the 15.

"15-17 weeks." Where I would want to take the average and input 16.

 

Uterine size values from 5 to 40, so there's a maximum of subexpression I would want to extract is a two digit number hyphen two digit number or basically [0-9] *[0-9] *[\-] *[0-9][0-9].

 

I'm willing to recode and replace by hand those that are really strange have a lot of text, but I'd love to at least get stata to do the most common variations which are where the numerical value is at the beginning of the expression and followed by either "c" or "cm" or "weeks" or "wk" (these are considered equivalent in gynecology) and can be ignored because there is essentially only one unit. 

I tried the following command: 

gen preophputsize = regexs(0) if regexm (size_bimannual_h_p, "[0-9]*[0-9]*[\-]*[
> 0-9]*[0-9]*[a-zA-Z]*$")

But then STATA wrote "regexm not found."

 

 

So my questions are:

1) how to extract the two digit uterine size in the simplest case "15 wk size" --> 15

(this would be enough!)

2) how to extract the two digit uterine size in the more complicated case and then take the average "15-17 wk size" --> 16

 

If this is too much for the forum, that's cool, I appreciate pointing me in the right direction!

Juno

 

 

 

 

 

Re: Horribly dirty variable

by Mark Pletcher -

You could use the ignore option and try to do as well as you can, and then use the force option, and see what Stata comes up with.  Then go through manually all the values that don't immediately look right?

Re: Horribly dirty variable

by Juno -

This worked! Thank you so much. I built up the ignore command and was able to force restring and capture a lot of the numerical data and only hand to code individuals a little bit. A lot better than what I was trying to do by brute force. Thanks!! Juno (The data cleaning continues :))