3

In SPSS, how do I pull forward longitudinal data to blank cells for each id?

Example: 

ID Time Gender
1  1    M
1  2    .
1  3    .
2  1    F
2  2    .
2  3    .
3  1    .
3  2    F
3  3    .

How do I get to this?

ID Time Gender Gender_Recoded
1  1    M      M
1  2    .      M
1  3    .      M
2  1    F      F
2  2    .      F
2  3    .      F
3  1    .      .
3  2    F      F
3  3    .      F

and then once I am able to carry forward, is there a command to do the reverse like in example 3, 1 so that F will pull up?

Thank you!!!

eli-k
  • 10,898
  • 11
  • 40
  • 44
Tamala Bradham
  • 31
  • 1
  • Welcome to Stack-Overflow! If one of the answers solves your problem, please select it as the solution (click the V next to it). Also please upvote any answers that you find useful. – eli-k Jun 14 '20 at 15:12

2 Answers2

2

You can use the AGGREGATE function to do this. You specify which variable to group by setting /BREAK and then create your aggregated variables using Aggregate Functions.

In this case, FIRST() will apply the first non-missing value within each ID to all the rows/observations with that same ID. Since it takes the first non-missing value, it both "carries forward" and "carries backward" the Gender value associated with each ID (solving both your cases).

AGGREGATE
 /OUTFILE=* MODE=ADDVARIABLES
 /BREAK=ID
 /Gender_Recoded=FIRST(Gender) .
user45392
  • 610
  • 7
  • 16
  • excellent solution - will also work with `max` instead of first. – eli-k Jun 14 '20 at 06:20
  • You are brilliant!!!! Thank you so much!!!! Please post this on the internet - I spoke to so many people, searched for months trying to find this coding...even spoke to IBM SPSS Tech dept...Thank you! – Tamala Bradham Jun 14 '20 at 12:03
  • 1
    Glad it helped! If one of the solutions provided answered your question please consider accepting it as the answer and/or upvoting any helpful answers for you. :) – user45392 Jun 14 '20 at 23:27
2

The solution @user45392 suggested is perfect, but here is another nice one:

First we'll put the existing values in the first line of every ID, then for repeating ID's we copy gender value from the row above:

sort cases by ID gender (d). 
if ID=lag(ID) gender=lag(gender).
eli-k
  • 10,898
  • 11
  • 40
  • 44