SPSS/macro: split string into multiple variables

0
votes

I am trying to split a string variable into multiple dummy coded variables. I used these sources to get an idea of how one would achieve this task in SPSS:

But when I try to adapt the first one to my needs or when I try to convert the second one to a macro, I fail.

In my dataset I have (multiple) variables that contain a comma seperated string that represents different combinations of selected items (as well as missing values). For each item of a specific variable I want to create a dummy variable. If the item was selected, it should be represented with a 1 in the new dummy variable. If it was not selected, that case should be represented with a 0. Different input variables can contain different numbers of items.

For example:

ID VAR1 VAR2 DMMY1_1 DMMY1_2 DMMY1_3
1 1, 2 8 1 1 0
2 1 1, 3 1 0 0
3 3, 1 2, 3, 1 1 0 1
4 2, 8 0 0 0

Here is what I came up with so far ...


* DEFINE DATA. 

DATA LIST /ID 1 (F) VAR1 2-5 (A) VAR2 6-12 (A).
BEGIN DATA
11, 28
21   1, 3
33, 12, 3, 1
4    2, 8
END DATA.

* MACRO SYNTAX.

* DEFINE VARIABLES (in the long run these should/will be inside the macro function, but for now I will leave them outside).
NUMERIC v1 TO v3 (F1).
VECTOR v = v1 TO v3.
STRING #char (A1).

DEFINE split_var(vr = TOKENS(1)).
    !DO !#pos=1 !TO char.length(!vr).
        COMPUTE #char = char.substr(!vr, !#pos, 1).
        !IF (!#char !NE "," !AND !#char !NE " ") !THEN
            COMPUTE v(NUMBER(!#char, F1)) = 1.
        !IFEND.        
    !DOEND.
!ENDDEFINE.

split_var vr=VAR1.
EXECUTE.

As I got more errors than I can count, it's hard to narrow down my problem. But I think the problem has something to do with the way I use the char.length() function (and I am a bit confused when to use the bang operator).

If anyone has some insights, I would really appreciate some help :)

2
macrosspss
The dummy variables in your example don't seem to correspond with either of your original delimited text variables. - eli-k

2 Answers

1
votes

There is a fundamental issue to understand about SPSS macro - the macro does not read or interact in any way with the data. All the macro does is manipulate text to write syntax. The syntax created will later work on the actual data when you run it.
So, for example, Your first error is using char.length(!vr) within the syntax. You are trying to get the macro to read the data, calculate the length and use, but that simply can't be done - the macro can only work with what you gave it. Another example in your code: you calculate #char and then try to use it in the macro as !#char. So that obviously won't work. ! precedes only macro functions or arguments. #char, in your code, is neither, and it can't become one - can't read the data into the macro...

To give you a litte push forward: I understand you want the macro loop to run a different number of times for each variable, but you can't use char.length(!vr). I suggest instead have the macro loop as many times as necessary to be sure you can deal with the longest variable you'll need to work with.

And another general strategy hint - first, create syntax to deal with one specific variable and one specific delimiter. Once this works, start working on a macro, keeping in mind that the only purpose of the macro is to recreate the same working syntax, only changing the parameters of variable name and delimiter.

1
votes

With my new understanding of the SPSS macro logic (thanks to @eli-k) the problem was quite easy to solve. Here is the working solution.

* DEFINE DATA. 

DATA LIST /ID 1 (F) VAR1 2-5 (A) VAR2 6-12 (A).
BEGIN DATA
11, 28
21   1, 3
33, 12, 3, 1
4    2, 8
END DATA.

* DEFINE MACRO.
DEFINE @split_var(src_var = !TOKENS(1) 
                        /dmmy_var_label = !DEFAULT(dmmy) !TOKENS(1)
                        /dmmy_var_lvls = !TOKENS(1))
    NUMERIC !CONCAT(!dmmy_var_label,1) TO !CONCAT(!dmmy_var_label, !dmmy_var_lvls) (F1).
    VECTOR #dmmy_vec = !CONCAT(!dmmy_var_label,1) TO !CONCAT(!dmmy_var_label, !dmmy_var_lvls).
    STRING #char (A1).
    LOOP #pos=1 TO char.length(!src_var).
        COMPUTE #char = char.substr(!src_var, #pos, 1).
        DO IF (#char NE "," AND #char NE " ").
            COMPUTE #index = NUMBER(#char, F1).
            COMPUTE #dmmy_vec(#index) = 1.
        END IF.
    END LOOP.
    RECODE  !CONCAT(!dmmy_var_label,1) TO !CONCAT(!dmmy_var_label, !dmmy_var_lvls) (SYSMIS=0) (ELSE=COPY).
    EXECUTE.
!ENDDEFINE.

* CALL MACRO.
@split_var src_var=VAR2 dmmy_var_lvls=8.