Extract Digits from String Variable

Recently, one of our clients used a text field for asking his respondents’ ages. The resulting age variable is in age-in-string.sav, partly shown below.

SPSS Extract Digits From String

I hope you realize that this looks nasty:

For adding injury to insult, the data contain 3,895 cases so doing things manually is not feasible. However, we'll quickly fix things anyway.

Inspect Frequency Table

Let's first see which problematic values we're dealing with anyway. So let's run a basic frequency table with the syntax below.

*Check which (string) values are present in age.

frequencies age
/format dfreq.


If we scroll down our table a bit, we'll see some problematic values as shown below.

SPSS Frequency Table Age

This table shows us 2 important things:

most values that can be corrected start off with 2 digits;
at least one value is preceded by a leading space.

Let's first remove any leading spaces. We'll simply do so by running compute age = ltrim(age).

Extract Leading Digits

We'll now extract any leading digits from our string variable with the syntax below.

*Create new string variable of length 3 -assume that nobody is older than 999 years....
string nage (a3).

*Loop over characters in age and pass into nage if they are digits.
loop #ind = 1 to char.length(age).
do if(char.index('0123456789',char.substr(age,#ind,1)) > 0).
compute nage = concat(rtrim(nage),char.substr(age,#ind,1)).
end if.
end loop.

So what we very basically do here is

This last condition is needed for values such as “55 and will become 56 on 3/9” We need to make sure that no digits after “55” are added to our new variable. Otherwise, we'll end up with “555639” -an age perhaps only plausible for Fred Flintstone.

Inspect Which Values Couldn't be Converted

Let's now inspect which original age values could not be converted. We'll rerun our frequency distribution but we'll restrict it to respondents whose new age value is still empty.

*Include only respondents without nage in next table.

select if (nage = '').

*Check which age values weren't converted yet.

frequencies age
/format dfreq.


Surprisingly, a quick scroll down our table shows that we can reasonably convert only a single unconverted age value: “Will become 56 on the 3rd of September:-)”

SPSS Adjust Single Data Value

It is probably safe to infer from this statement that this person was 55 years old at questionnaire completion. We'll set his age to 55 with a simple IF command. We'll then run a quick final check.

*Manually correct single age value.

if(char.index(age,'Will become 56') > 0) nage = '55'.

*Recheck which age values weren't converted yet.

select if (nage = '').

frequencies age
/format dfreq.

Final Frequency Table

As shown below, our minimal corrections resulted in a mere 148 (out of 3,895) unconverted ages. A quick scroll down our table shows that no further conversions are possible.

SPSS Valid Values For Frequencies

We'll now convert our new age variable into numeric with ALTER TYPE and inspect the result.

*Convert nage to numeric.

alter type nage(f3).

*Check age distribution.

frequencies nage

*Exclude nage = 99 from all analyses and/or editing.

missing values nage (99).

Inspect Final Results

First off, note that our final age variable has N = 148 missing values -just as expected. It is important to check this because ALTER TYPE may result in missing values without throwing any error or warning.

Next, a histogram over our final age values is shown below.

SPSS Histogram Age Distribution

Although the age distribution looks plausible, the x-axis runs up to 120 years. SPSS often applies a 20% margin on both sides so this may indicate an age around 100 years.

Closer inspection shows that somebody reported an age of 99 years. As we think that's not plausible for the current study, we set it as a user missing value.


Thanks for reading!

Tell us what you think!

*Required field. Your comment will show up after approval from a moderator.