spss

Fill in missing values in a row based on another column SPSS syntax?

I would like to fill missing row values with values from one designated column using spss syntax.

Current Table:(Measure_Table)

sID Visit value_1 value_2 value_3 value_Score whyMissing
1 1 3 2 5 12
1 2 1 2 5 12
1 3 6 2 5 12
2 1 8 2 5 12
2 2 -30
2 3 -10
3 1 2 2 1 12
3 2 7 8 9 12
3 3 -10

I have been trying to use this syntax, but when I do, it runs but the values from whyMissing do not populate into the other columns. It is important to me to do this with multiple columns because my dataset is larger than what I have shown here.

Desired Outcome Table:(Outcome_Measure_Table)

sID Visit value_1 value_2 value_3 value_Score whyMissing
1 1 3 2 5 12
1 2 1 2 5 12
1 3 6 2 5 12
2 1 8 2 5 12
2 2 -30 -30 -30 -30 -30
2 3 -10 -10 -10 -10 -10
3 1 2 2 1 12
3 2 7 8 9 12
3 3 -10 -10 -10 -10 -10

This is the syntax that has been able to run, but it will not populate the values when I export to spss. If this was python, I would just use fill na! I am open to other approaches using spss syntax! If I can do this using the interface and past the syntax to use later that is okay too!

My variable view has some of these missing values set as lo -- 0. Would that be getting in the way and how can I bypass?

DO REPEAT vr=value_1 TO value_Score.
    IF NOT MISSING(whyMissing) vr=whyMissing.
END REPEAT.
Solution

  • The problem is the way SPSS handles missing values!!!Thank you so much for your help with the loop above though.

    *recode the missing values to -9 (or whatever you want) that will be replaced by the whyMissing Variable.
RECODE value_1 TO value_Score (SYSMIS = -9). 

*create a list of variables you want to change. Then run if whyMissing has an error code, replace that variable.

do repeat vr=value_1 to value_Score.
  if whyMissing<0 vr=whyMissing.
end repeat.
EXECUTE.