Have you tried converting the .sav or .csv file to MS Excel? Give it a try and see if it works. If it does, better save the file in .xls or xlsx and try to reopen it SPSS.

Converting string to numeric data in SPSS will make you lose any data other than numbers. For example, If string cell value is  S1 it will be lost if converting it to numeric 

Regards

I first couldn't reproduce this problem using your example data.

SPPS (Version 22.0.0.1) read them all in as numeric variables

using the standard File/Read Text Data function via the GUI

and showed me all values.

However, as is stated on step 5 in the Text Import Wizard (when using

the mentioned function):

    "Data format is determined [automatically] from the values present in the

    first 200 records. If a column contains multiple data types [...],

    the variable is set to string."

I assume this might be happening in your case, namely that not all rows

contain the same format.

If your data was entered manually and probably by several people,

it might be that not all rows were entered equally, for

example some people might have entered the data using dot separators,

others using commas (2.5 vs. 2,5). Or there might be typos that

introduced alphabetic characters in your data.

Therefore you might have to check your data and clean it up first,

e.g. manually using a spreadsheet software like Excel or LibreOffice Calc,

via a text editor like Vim, or while they are string variables in SPSS.

Another idea also involves decimal separators, because it seems that you

only have problems with the variables containing decimal numbers.

That might be an issue with the decimal separator symbol specified in SPSS and the one in your data. For example, due to your set LOCALE based on the language/country you use in SPSS, the default separator might be a comma instead of the dot that you have in your data.

When I changed the separator to comma, it read in all variables and

specified them as numeric, but all decimal numbers where shown as

missing values.

You can change the separator indirectly by specifying another locale that

uses the dot separator (e.g. via the syntax "SET LOCALE = “en_US”"),

or directly without changing the locale using this syntax:

SET DECIMAL = DOT.

(Simply put it in a new syntax file and run it, then try to read your data

in again.)

Because you said that the data format of your problem variables were

specified as string by SPSS, these two issues might be combined in

your case:

The solution might then be:

I have dealt with this in the past and I think the following is a pretty safe way to deal with it, because you don't change your original variables, so you won't accidentally lose any data.

First you need to declare new numeric variables for each of the string variables as follows:

Numeric MyNewVar (f3.0).

NB the (f3.0) but is saying that MyNewVar  is going to be 3 digits long in total and will have no digits after the decimal place.  If you had put (f4.2) then it would mean that mynewvar would be 4 digits long overall, with two digits after the decimal place.  This means there  will at most 2 digits before the decimal place, so the largest value you can store in an (f4.2) format will be 99.99.

Then you need to run a command populate the numeric variable with values from the string variable  

MyNewVar = number(MyStringVar,F3.0).

(NB: The F3.0 matches the format for MyNewVar in the SPSS code I wrote above.  If you'd defined MyString Var to have format (f4.2) then you'd need to write

MyNewVar = number(MyStringVar,F4.2).

IIRC This will convert the numeric variables, but will leave the string values as missing values (system missing) and you will get also get an error message.  If you want the string values converted to particular numeric values you will need to use  do if-then-else commands to first process the string values and then convert the values which can be converted into numeric using the syntax above.

As I said, it's rather a long drawn out process.  If anyone has worked out how to do this more efficiently but without running the risk of accidentally losing any data, I'd really like to find out how they do it.

Hello Ali,

I notice that you asked your question some time ago, so you might have solved the problem by now, but unless I misunderstand your query I think it might have a simple solution.  In Variable view in SPSS, under the heading Decimals you simply need to indicate, for each variable, the number of decimal places you want your data to be presented as.  I think it would also be necessary to convert your data, again for each variable, to numeric in the Type column.

This is so simple, however, that I wonder whether I have misunderstood your problem.

Robt.

Hi,

It's an old thread, but I see there are similar questions. Having other locale than US I used syntax like this:

COMPUTE NewVar = NUMBER(REPLACE(OldVar, ".",","),F4.1).

EXECUTE.

All best,

RP

Hi,

open in excel or calc save as xls and then open in SPSS... I use to have this problems.. now I use R and that's problems it's history..

best

M

Hi Moises,

You are absolutely right, but I suppose that your solution will work only when a dot is set in a system or in office packages as a separator. It won't work when a comma is set as a separator. In such a situation numbers with dots are recognized in Excel / Calc as well as in SPSS as texts.

I also agree that R can be much more flexible in such situations, the same like Matlab or Python. But the question was about SPSS and the syntax I mentioned helped me during opening in a loop hundreds of files one by one, containing dots instead of commas. It is also possible to temporary change SPSS codepage using syntax:

PRESERVE LOCALE.

SET LOCALE='en_UK'.

GET DATA

(YourCsvFileDescription, Variables, Labels etc.)

ALTER TYPE YourVariable (F4.1).

RESTORE LOCALE.

It is also possible to put the above syntax in a loop.

All best,

Robert

Good evening, I just read your discussion about transforming string into numbers and I would like to ask another question to your experties:

is it possible to automatically assigne different numbers to different words within a string? More specifically, I have strings like the following:

Colomn1: colors

string1: yellow+brown+blue

string2: brown

string3: red+blue+black

Through DO IF CHAR.INDEX (colors, "yellow'') gt 0 COMPUTE ColorNo1 = 1

I already managed to extract the first occurring colour in each string and put it into another colomn as a number.. but how can I give the input to the system so it can automatically recognize the word 'yellow', 'brown', 'blue', 'red', or 'black' in the string and assign each one of them a number, thus classifying them into other colomns?

Final result should be ( if yellow=1, brown=2, blue=3, red=4, black=5):

ColorNo1 ColorNo2 ColorNo3 ColorNo4 ColorNo5

String1 1 2 3 0 0

String2 0 2 0 0 0

String3 0 0 3 4 5

Thank you very much in advance. Giorgia

That's an interesting problem. Can you give us a bit more information?

1) You put a plus sign between the colours when you have more than one colour in the string. Will the colours always be separated by a plus sign (or another predictable separator) if there's more than one in a string?

2) How consistent is the use of the separators (could spaces be included sometimes)?

3) Can the colours occur more than once in a string?

4) Do you want the numeric strings to show the order that the colours appear in the string, or do you just want to know whether the colour is there or not , or if it can occur more than once, how many times it appears in the string?

I have some ideas how I would approach this but all of these factors would effect my final decision. I'm fascinated to see other researchers' ideas as well.

Hi Giorgia,

Assuming the structure of given data as presented in the example you can first extract words using a loop:

STRING #dat(A20).

COMPUTE #dat = colors.

VECTOR word(3,A10).

LOOP #i = 1 to 2.

COMPUTE #plus = CHAR.INDEX(#dat,"+").

COMPUTE word(#i) = CHAR.SUBSTR(#dat,1,#plus-1).

COMPUTE #dat = CHAR.SUBSTR(#dat,#plus+1).

END LOOP.

COMPUTE word(3) = #dat.

EXECUTE.

DO IF word1 = "" AND word2 = "".

COMPUTE word1=word3.

COMPUTE word3="".

END IF.

ELSE.

DO IF word1 ~= "" AND word2 = "".

COMPUTE word2=word3.

COMPUTE word3="".

END IF.

EXECUTE.

* and recode:.

IF (ANY('yellow',word1 to word3)=1) ColorNo1 = 1.

IF (ANY('brown',word1 to word3)=1) ColorNo2 = 2.

IF (ANY('blue',word1 to word3)=1) ColorNo3 = 3.

IF (ANY('red',word1 to word3)=1) ColorNo4 = 4.

IF (ANY('black',word1 to word3)=1) ColorNo5 = 5.

RECODE ColorNo1 TO ColorNo5 (SYSMIS=0).

EXECUTE.

I am sure there are smarter solutions and I'll be delighted to know Jacqueline ideas.

All best,

Robert

Dear Jacqueline,

thank you for your interest. I'll try to answer to your questions, but first of all you have to know that the syntax that i wrote was just an example to make it easier to be understood, my actual strings are much more complicated (es. CEFTRIAXON INFUSIEPOEDER 2000MG FL + CIPROFLOXACINE INFVLST 2MG/ML (H2SO4) ZAK 100ML + GENTAMICINE INJVLST 40MG/ML AMP 2ML (only on 27.11), in which I would need to define as variables only "CEFTRIAXON", "CIPROFLOXACINE"' and "'GENTAMYCINE").

1) You put a plus sign between the colours when you have more than one colour in the string. Will the colours always be separated by a plus sign (or another predictable separator) if there's more than one in a string? THE ANSWER IS YES.

2) How consistent is the use of the separators (could spaces be included sometimes)? SPACES ARE ALWAYS INCLUDED

3) Can the colours occur more than once in a string? NO, YOU CAN ONLY FIND ONE "COLOR"PER STRING.

4) Do you want the numeric strings to show the order that the colours appear in the string, or do you just want to know whether the colour is there or not , or if it can occur more than once, how many times it appears in the string? i WOULD LIKE TO KNOW WHETHER THE "COLOR" IS THERE OR NOT.

In the end, in order to be "quicker", I created 31 dummy variables (one for each "'color") with 0/1 values to see if the "color"is there or not... Of course this is just a temporary solution which has been useful so far, but I am still interested in another way in order to obtain different number codes for different "colors".

Dear Robert,

also thank you for your answer. Your loop is very complicated for me, I am just a beginner with SPSS. But I will try to apply that, but I have a question: wouldn't I loose all my data in the way they appear right now using your syntax?

Thank you very much.

Giorgia.

Dear Giorgia,

For sure you won't loose your data but you should always do a copy. of a file with data before significant transformations. To check if the solution with loops work you need only to copy and paste the whole syntax to syntax editor and exchange colors onto medicines.

In case of troubles it would be best if you will present a couple of real rows from your original database.

All best,

Robert

And please notice that the construction of the loop assumes three columns in data and the searched text in recoding part, this in apostrophes after:

IF (ANY('

have to be exactly the same like the whole text between ++. It means that you should include spaces. For example:

IF (ANY(' CIPROFLOXACINE INFVLST 2MG/ML (H2SO4) ZAK 100ML ',word1 to word3)=1) MedicineNo4 = 4.

differs from:

IF (ANY('CIPROFLOXACINE INFVLST 2MG/ML (H2SO4) ZAK 100ML ',word1 to word3)=1) MedicineNo4 = 4.

and from:

IF (ANY(' CIPROFLOXACINE INFVLST 2MG/ML (H2SO4) ZAK 100ML',word1 to word3)=1) MedicineNo4 = 4.

In case of trouble with the number of spaces the syntax can be easily changed to be more convenient.

The number of medicines is not a matter, but the dosage differs searched strings.