Keeping records with 80% or more data

[Nikki Dee]

Hi, I have a coding question regarding keeping records that meet the criteria where at least 80% of the data is not missing. I want to delete any records that are missing more than 20% of the data.

recode Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21
Q22 Q23 Q24 Q25 Q26 (0=0) (1=1) (2=2) (3=3) (4=4)
(5=5) (777=.) (888=.) (999=.)

In this case, there are 26 variables and I want to keep only those records where 21 or more of these variables are not missing. The code I have in SPSS looks like this:

COUNT BaselineTotal=Q1 TO Q26 (1 THRU 5).
FILTER OFF.
USE ALL.
SELECT IF (BaselineTotal>=21).
EXECUTE.

Any help would be great.

Thanks.

[Mike Lacy]

A literal translation of the SPSS code, if I still remember it:

recode Q1-Q26 (777 888 999 = .)
egen BaseLineTotal = anycount(Q1-Q26), values(1/5)
keep if (BaselineTotal >= 21)

See also -help egen- and the -rowmissing- function there for alternative approaches.

Deleting observations is almost always a bad idea. It would be better to not use the -keep if= and simply use the -if- qualifier to restrict analysis to the desired observations, e.g.:

summarize Whatever if (BaselineTotal >= 21)

Finally, presuming that an analysis of only the observations with good data are representative is dubious, but that's a question that goes beyond the current scope.

[Nick Cox]

Mike's advice and code are very good, but the first line (which echoes #1) is not needed as values 777 888 999 are going to be ignored any way by the next egen calculation. If 777 888 999 code different kinds of missings, it even might be prudent to recode those to .a .b .c or other extended missing values. For example, there could be some juice in "not relevant" "did not understand the question" "refused to answer" etc.