SPSS Tutorials

BASICS REGRESSION T-TEST ANOVA CORRELATION

SPSS – Splitting a String Variable

After importing some data into SPSS, some answers ended up in a single string variable. The data are in splitstrings.sav, part of which is shown below. Excel has a nice “text to columns” function to split it but SPSS hasn't... So you think you can syntax? Then let's go and split this string into the original answers.

SPSS Split String Text To Columns Data View

Step 1 - New String Variables

First off, I'll make two guesses:

If my guesses are right, then 30 new string variables -each of length 25- should be able to hold all answers. But no worries: if my guesses are wrong, I'll find out after performing the string splitting exercise.

Step 2 - Split String Syntax

We'll basically split our string by combining VECTOR and LOOP in a somewhat unusual way. Those who find it difficult may want to take a look at this long version with comments.

*New string variables: create more and make them longer than you expect to need.

string emo_1 to emo_30 (a25).

*Split string: each ";" indicates a new answer.

string #char(a1).
compute #index = 1.
vector emo = emo_1 to emo_30.

loop #pos = 1 to char.length(emotions).
compute #char = char.substr(emotions,#pos,1).
if(#char <> ";") emo(#index) = concat(rtrim(emo(#index)),#char).
if(#char = ";") #index = #index + 1.
end loop.
execute.

Result

SPSS Split String Text To Columns Result

Step 3 - Check Results

For each of our 30 new emo_ variables, we'll now create a numeric variable which holds the lengths of the string values. If my guesses about the number of answers and their lengths were right, then

Syntax for Checking Results

*For each new string, create numeric variable indicating string lengths.

do repeat #old = emo_1 to emo_30 / #new = len_1 to len_30.
compute #new = char.length(#old).
end repeat.

*Variables having max <= 24 have not been truncated because our new strings all have length 25.

descriptives len_1 to len_30.

Result

SPSS Split String Variable Text To Columns Descriptives

Only the first 15 new variables contain non zeroes. Retrospectively, just 15 (not 30) new string variables would have been enough. Hey, but doesn't everything always look better retrospectively?
Second, none of our new string variables holds any value longer than 9 characters/bytes. No values were truncated. These two checks confirm that our operation has fully succeeded. Let's now clean things up a bit.

Step 4 - Remove Redundant Variables

*Variables having max = 0 indicate empty string variables: emo_16 and onwards. We'll delete those and our temporary length variables.

delete variables emo_16 to len_30.

*We'll now set all string variables to their minimum required length.

alter type all(a=amin).

Split Syntax with Comments

For those who found the splitting syntax a bit hard, I added some comments to the version below. Tip: copy-paste it into Notepad++ for easier reading.

*New string variables: create more and make them longer than you expect to need.

string emo_1 to emo_30 (a25).

*As we'll loop over characters in original string, we'll pass each character into scratch variable #char for easier reference.
string #char(a1).

*Set up a vector. New string variables can now be referenced as emo(1), emo(2) and so on.
vector emo = emo_1 to emo_30.

*Set up counter #index for new string variables.
compute #index = 1.

*Now emo(#index) refers to emo_1. After adding 1 to #index, emo(#index) = emo_2. And so on.

*Loop over all characters in original string.
loop #pos = 1 to char.length(emotions).

*Pass character into #char.
compute #char = char.substr(emotions,#pos,1).

*If #char is not ";", add character to end of emo variable that's being constructed.
if(#char <> ";") emo(#index) = concat(rtrim(emo(#index)),#char).

*If char is ";", continue with next new string variable.
if(#char = ";") #index = #index + 1.
end loop.
execute.

Final Notes

I hope you enjoyed this fun little exercise as much as I did. But you probably didn't.

Thanks for reading!

Previous tutorial: SPSS EXECUTE – What and Why?

Next tutorial: Inferential Statistics – Quick Introduction

Let me know what you think!

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

This tutorial has 3 comments