Replacing characters in string variables

[Erik]

Hi,

I've imported a number of variables from Excel into SPSS. One of them
contained numerical values with decimals preceded by commas (European
notation) as well as dots (US notation), and since SPSS cannot handle
both notations in the same variable, it was converted into a string.
Now, I would like to replace the dots by commas and change the type of
variable to numerical, in order to use it in analyses. How can I do
that? I cannot find a command in SPSS-syntax that is similar to the
'Replace'-command in Excel.
The SPSS-file contains over 700,000 rows so I would like to avoid
re-importing the variables from Excel or recoding the values manually.

thanx, Erik

[Bruce Weaver]

You can use the INDEX function to find the position of the
comma in a string (it will be 0 if no comma). Then you can
use SUBSTR to pull out the various bits you need, and CONCAT
to put them together again, replacing the comma with a
period. The following seems to work.

* --- Start of syntax ----- .

DATA LIST LIST /x.str (a5) .
BEGIN DATA.
",1239"
"2.456"
".789"
"1,246"
END DATA.

list.

compute commapos = index(x.str,',').
exe.
list.

string x.str2 (a5).
do if (commapos=1).
- compute x.str2 = concat('.',substr(x.str,2)).
else if (commapos > 1).
- compute x.str2 =
concat(concat(substr(x.str,1,commapos-1),'.'),substr(x.str,commapos+1)).
else.
- compute x.str2 = x.str.
end if.
recode x.str2 (convert) into x.
exe.
format x(f8.4).
list.

* --- End of syntax ----- .

Cheers,
Bruce
--
Bruce Weaver
wea...@mcmaster.ca
www.angelfire.com/wv/bwhomedir/

[Jonathan Fry]

"Erik" <eriklee...@zonnet.nl> wrote in message
news:1b9f60c7.03100...@posting.google.com...

Erik,

You can avoid the replacement problem by selecting your format according to
which decimal separator appears. It might look like this:

do if (index(stringfield, ',')).
* comma was found, so European style assumed.
compute num = number(stringfield, dot12).
else
* no comma found, so US style assumed.
compute num = number(stringfield, f12).
end if.
format num(f12.2).

Jonathan Fry
SPSS Inc.

[Bruce Weaver]

That's neat. So the example i posted earlier can be
simplified to the following:

* ---- Start of syntax ---- .

DATA LIST LIST /x.str (a5) .
BEGIN DATA.
",1239"
"2.456"
".789"
"1,246"
END DATA.

do if (index(x.str, ',') > 0).
- compute x = number(x.str, dot12).
else.
- compute x = number(x.str, f12).
end if.
format x(f12.4).
list.

* ---- End of syntax ---- .

Cheers

[Erik]

Thanx a lot Bruce and Jonathan!
It works perfectly :)

Grtz, Erik