splitting multiple values in one variable to several variables

[IsabelM]

Hi,

I use several online measurement instruments for my research and for some questions the respondent can select several options. When exporting the data all the selected options are put into one variable. So I end up with variables which contain more than one value.
I wonder whether it is possible in SPSS to split these values into several variables so I have one value per variable for analysis. I now do this by copying colums of data to excel and then transform data to text and then copying it back to excel, but this takes a lot of time.

I'd be very gratefull for any tips!

Isabel email: imarrozos@trajectum.info

[helicon]

This is fairly trivial to accomplish if you have a passing familiarity with SPSS syntax. The general approach is to treat the value as a string variable in order to parse it into separate numerical variables. Have a go at adapting this example to your needs. If you have trouble, post back with several examples of your data now and how you want it to appear and I'll help you further.

[IsabelM]

@helicon,

Thanks so much for your reply but I cannot work it out from the syntax example you have shown me. I would like to attach an example of the data but can''t upload an spss file? How can I get an SPSS file example to you??

[helicon]

Just something like this:

Now       ->    After
oldvar            v1      v2     v3     v4
------            ---     ---    ---    ---
1234              1        2      3       4
5423              5        4      2       3
952               9        5      2
etc.

If the desired values are always of equal length (i.e. always single digit or two digits etc), it's easy. If they're delimited by some character, it's easy. If they have varying lengths it can be a bit more tricky. I just need to see enough of a sample to understand if the logic will hold across all your cases.

[IsabelM]

I've added an example in excell. Up till now I've split the variable in excel before reading it in to spss but I would rather do it in SPSS using a syntx as this would save me such a lot of work.

So for example variable C254 or C256 I would like to split the values delimited by ; into separate variables.

Again thanks so much for your time and help.

[helicon]

OK, first of all the syntax was sourced from here so all credit goes to the original author.

As you seem to have many variables where this will need to be applied, it is best to use a macro so that it can be called once for each applicable variable rather than having to copy and paste the syntax and swap out the variable names. The following will split the variable into a predefined number of values (choose the maximum number of possible answers, currently set at 5) and write the new variables at end of the list. C254 will become C2541, C2542, C2543, C2544, C2545.

Example data:

data list list / c254 (a30) c256 (a30).
begin data.
4.0000;3.0000;8.0000;9.0000  5.0000;10.0000
1.0000;2.0000  9.0000;8.0000;7.0000
end data.

Macro:

DEFINE !parse (var=!TOKENS(1) /nbval=!TOKENS(1))
COMPUTE !var=CONCAT(RTRIM(!var),';').
STRING #str(A8).
VECTOR !var (!nbval F8.0).
COMPUTE #beg=1.
LOOP #cnt=1 TO !nbval.
+COMPUTE #str=SUBSTR(!var,#beg).
+COMPUTE #end=INDEX(#str,';')-1.
+DO IF #end=-1.
+ BREAK.
+END IF.
+COMPUTE !var(#cnt)=NUMBER(SUBSTR(#str,1,#end),F8.0).
+COMPUTE #beg=#beg+#end+1.
END LOOP IF #end=-1.
EXECUTE.
!ENDDEFINE.

* Call the macro.
!parse var=c254 nbval=5.
!parse var=c256 nbval=5.

You'll need to append this '!parse var=VAR nbval=5.' to the file for each variable you want to split, filling in the appropriate variable name and number to split where red. You'll also get errors when cases are empty, but these can be ignored.

[locoTom]

Hi all.

I have almost the same problem but with a string of words separated by empy spaces.
Can you help me with an update of the spss macro to this case.
I'm attaching a file with some example rows.

Thank you very much!!!

[locoTom]

Finally I have created an adaptation of the SPSS Macro to my problem, the code is

DEFINE !parse2 (var=!TOKENS(1) /nbval=!TOKENS(1))
COMPUTE !var=CONCAT(RTRIM(!var),' ').
STRING #str(A30).
VECTOR !var (!nbval A30).
COMPUTE #beg=1.
LOOP #cnt=1 TO !nbval.
+COMPUTE #str=SUBSTR(!var,#beg).
+COMPUTE #end=INDEX(#str,' ')-1.
+DO IF #end=-1.
+BREAK.
+END IF.
+COMPUTE !var(#cnt)=SUBSTR(#str,1,#end).
+COMPUTE #beg=#beg+#end+1.
END LOOP IF #end=-1.
EXECUTE.
!ENDDEFINE.

same way to call the Macro.

I hope this could help anyone with similar problem.
I include a file with an example of macro's work

Best Regards.