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.

[CgRuiz]

Thank you guys. These macros work perfect. However, I was wondering if the macro can be updated to split the variable into several variables and named them based on their content.

For instance, this is the variable V_1 to be parsed (data separated by //):

V_1: CURRENT MEDICATION: ADVIL // PAST MEDICATION: LYRICA // DATE LAST WORK: 01-14-83 // RETRAINING: SECRETARY

I would like to obtain the new following variables:

V_CURRENT_MEDICATION: ADVIL
V_PAST_MEDICATION: LYRICA
v_DATE_LAST_WORK: 01-14-83
V_RETRAINING: SECRETARY

Thank you so much.

[helicon]

The straightforward thing to do would be to run the macro as is and just retain the parts of the strings you're interested in then rename the variables. Append the following syntax beneath the macro:

do repeat #x = V_11 to V_14.
compute #y = char.index(#x, ":").
compute #z = char.length(#x).
compute #x = substr(#x, #y+2, #z-#y).
end repeat.
compute V_DATE_LAST_WORK = number(V_13, adate8).
formats V_DATE_LAST_WORK (adate8).
exe. 

rename variables (V_11 = V_CURRENT_MEDICATION) (V_12 = V_PAST_MEDICATION) (V_14 = V_RETRAINING).
delete variables V_13.

[RickyJamesMD]

Hi, friends,

The posts above were super helpful and I've tried playing around with it. Still, I'm having great difficulty manipulating the codes to my scenario, and would appreciate your expertise.

Essentially, this is what I am trying to do: Split a string into new variables based on the position of the information within a string. Example:

oldvar        -->      var1    var2    var3  var4
661234                   6       6      1     234
892123                   8       9      2     123
413433                   4       1      3     433
458739                   4       5      8     739

I know it's a trivial concept but I've already spent 3 days searching the web for a solution.

Cheers,
Rick

[RickyJamesMD]

Hey guys, I think I've found a solution to my previous question:

COMPUTE var1=SUBSTR(oldvar,1,1).
COMPUTE var2=SUBSTR(oldvar,2,1).
COMPUTE var3=SUBSTR(oldvar,3,1).
COMPUTE var4=SUBSTR(oldvar,4,3).
Execute.

[Cinema.Bianchini]

@ Helicon:

Hi! I hope you are well!

I am not very familiar with SPSS syntax at all, but in my dataset I have a a string variable that I need to parse it into separate numerical variables. The desired values are divided by semicolumns. Ex: 493.90;521.00;V15.85 or 293.83;296.22;724.5;V15.85;V70.0. Desired values are not always of the same lenght and are not always the same in number within the text unparsed variable. I tied to adapt your syntax from the previous examples but it did not work. The text variable name is @_x000D_Diagnoses. Any chance you could help me?

Many thanks!

[helicon]

Here you go:

Note, this assumes that the number of variables to be parsed is no more than 10. If it is more then change the 10 on the third line to whatever the maximum number of variables is.

string #diagnoses (a250).
compute #diagnoses=concat(rtrim(@_x000D_Diagnoses), ";").
vector @_x000D_Diagnoses(10, a20).
compute #i = 1.
loop if char.index(#diagnoses, ";") gt 0.
compute #ind=char.index(#diagnoses, ";").
compute @_x000D_Diagnoses(#i)=substr(#diagnoses,1, #ind-1).
compute #diagnoses=substr(#diagnoses, #ind+1).
compute #i = #i + 1.
end loop.
exe.

[Lloyd.Banwart]

Hi all, I'm late to this ballgame, but I have found these posts to be very helpful. The issue I am running into is that after calling the macro my data is being truncated by one var. i.e. with the 2 and 15 in problem not outputting and the 10 and 3 in race not outputting.

----------

DATA LIST LIST /problem(A12) race(A12).
BEGIN DATA
'1 2' '4 5 6 9 10'
'10 11 3 15' '5 2 3'
END DATA.
LIST.


* Define a macro to do the job.
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.

* Now call the macro.
!parse var=problem nbval=5.
!parse var=race nbval=5.