Replace missing values with previous or following nonmissing value

#1
Hello,

I have a large data set with 1.8 million records and ~ 500,000 individuals, many with multiple records. Some records for individuals are missing information that are contained in another record for that same person. For example:

ID DOB GENDER ETHNICITY

1 2/4/08 F
1 F 1
1 2/4/08

I am looking for a way in SPSS to fill in the missing ETHNICITY for the first record, DOB for the second, and GENDER and ETHNICITY for the third based on the information contained in the other records.

Please help!

Thank you in advance!

LL
 
#3
Thank you, Karabiner. This worked great for the numeric variables, but not the string variables. It's street address that I am trying to fill in and some have different addresses as time went on. I'd like to take the most recent (last) street address, but when I selected last street it didn't fill in if the last record for an individual was blank. When I sorted by ID and street, the most recent street was no longer the last record (and I don't have dates on them unfortunately). Any suggestions for this?

Thank you so much!

Regards,

LL
 

Karabiner

TS Contributor
#4
I'd like to take the most recent (last) street address, but when I selected last street it didn't fill in if the last record for an individual was blank. When I sorted by ID and street, the most recent street was no longer the last record (and I don't have dates on them unfortunately).
I must admit that I didn't fully understand how the data
are ordered. But if you want to substitute a blank "street"
name with the street name of the previous record, then
you can use LAG
IF (street='' AND Id=LAG(Id) ) street = LAG(street) .

HTH

K.
 
#5
I was actually able to get the aggregate command to work for the street variables. I'm not sure what was wrong initially.

Thanks again!!

LL