Search code examples
macrosspss

Loop for sorting and linear interpolating data in SPSS

I often replace missing values in SPSS using a linear interpolation after sorting the data by another variable. For example, I may sort my data using a column named Var_A_Sort, and then linear interpolate missing values within a column named Var_A. I then repeat the process by sorting my data using a column named Var_B_Sort and linear interpolate missing values within a column named Var_B, and so on. Below is the syntax I use to complete the process.

SORT CASES BY Var_A_Sort (A).
EXECUTE.

RMV
 /Var_A=LINT(Var_A).
EXECUTE.  

SORT CASES BY Var_B_Sort (A).
EXECUTE.

RMV
 /Var_B=LINT(Var_B).
EXECUTE.

I often use a macro in SPSS to repeat tasks for variables in a list (such as the example below). However I am not sure how I can create a macro or write code in Python within SPSS syntax which which could be used to complete the task described above.

DEFINE !RegLoop (Ylist = !CMDEND)
!DO !Y !IN(!Ylist)

    RMV
     /!Y=LINT(!Y).
    EXECUTE. 

!DOEND
!ENDDEFINE.

!RegLoop Ylist = Var_A Var_B.
Solution

  • Using !concat in the macro will solve the problem. You can stay with the original macro call if you define the macro like this:

    DEFINE !RegLoop (Ylist = !CMDEND)
!DO !Y !IN(!Ylist)
SORT CASES BY !concat(!Y, "_Sort") (A).
RMV / !Y = LINT( !Y ).
!DOEND
EXECUTE. 
!ENDDEFINE.

!RegLoop Ylist = var_A var_B .

    But you can use !concat to write even more flexible macros with more complex variable names. For example:

    DEFINE !RegLoop (Ylist = !CMDEND)
!DO !Y !IN(!Ylist)
SORT CASES BY !concat("Var_", !Y, "_Sort") (A).
RMV / !concat("Var_", !Y) = LINT( !concat("Var_", !Y) ).
!DOEND
EXECUTE. 
!ENDDEFINE.

!RegLoop Ylist = A B .