Tell us more about the variables.

If the other values are numeric. You could use use ALTER TYPE on the variables and the string will be turned into sysmis (system missing). Syntax would be 'ALTER TYPE var0001 to var2000 (F5.0)'. Exact format would depend on your requirements. This would call for 5 positions without decimals. In a next step, sysmis could be changed into 999 and assigned a missing value. If the rest are numeric values, you should have assigned a missing numeric code in advance. Not doing so, is a sign of bad data management.

Otherwise, you might want to use a DO REPEAT END REPEAT loop to change or count the string for missing into a new variable. What you need, would depend on the variables in your file.

This page seems to answer your question: https://www.ibm.com/support/pages/how-do-i-select-cases-spss-windows-using-either-syntax-or-windows

Hello Daniel,

You could save your data file as a .CSV file and read it into R. Here's R code that will list the variables in which one or more missing values were found (assuming that 'MD' continues to be your signal for a missing data point). It's not really elegant, but it works (the first half just generates a sample data set):

# picking variables with missing values

# string variables; 'MD' is the target missing data indicator

#

# construct sample data frame; rows = cases, col's = variables

#

v1

Just for the sake of brevity, in R, once you have the x created as in David Morse 's answer, this can be done in one line of code:

x[,apply(x,2,function(x) any(x=="MD"))]

Come on, folks! There's no need for someone has and uses SPSS to switch to R for a relatively simple problem. At least I think it's a relatively simple problem. Unfortunately, Daniel (Mello, not Wright) has not explained it very clearly, so we're all guessing what it really is.

Daniel Mello, please provide a short listing of what the data look like now, and what you want it to look like AFTER your SELECT IF command.

Based on the non-working code you posted, I wonder if you want something like this?

SELECT IF ANY('MD', V1 to Vn).

EXECUTE.

This will delete all rows where 'MD' is not found for at least one of the variables V1 to Vn. Is that what you want to do?

EDITED: Clarified that I was addressing Daniel Mello, not Daniel Wright. ;-)

I missed this bit the first time I looked at the original post:

Endgoal: I want a list of the variables that have at least one cell with the missing data indicator.

If I understand, something like this will do the trick. Notice that only v2 has any instances of 'MD'. So it should be the only variable excluded from the variable list at the end.

* Generate a small dataset to illustrate.

NEW FILE.

DATASET CLOSE ALL.

DATA LIST LIST / v1 to v3 (3A2).

BEGIN DATA

aa bb cc

dd ee ff

gg hh ii

jj MD kk

END DATA.

DATASET NAME original.

LIST.

MISSING VALUES v1 to v3 ("MD").

* Use AGGREGATE to get the number of missing values for each variable.

DATASET DECLARE Agg.

AGGREGATE

/OUTFILE='Agg'

/BREAK=

/v1=NUMISS(v1)

/v2=NUMISS(v2)

/v3=NUMISS(v3).

DATASET ACTIVATE Agg.

* Now transose the file to get the original variable names as values of a new variable.

FLIP VARIABLES=v1 v2 v3.

DATASET NAME Flip WINDOW=FRONT.

DATASET CLOSE Agg.

* Keep only the variable names where the number of missing values = 0.

SELECT IF var001 EQ 0.

RENAME VARIABLES (CASE_LBL=VarsWithNoMD).

LIST VarsWithNoMD.

* End of code.

OUTPUT from the final LIST command:

VarsWithNoMD

v1

v3

Number of cases read: 2 Number of cases listed: 2

Is that what you want?

Bruce Weaver , I wasn't recommending or not R, just providing more concise code. Here is David Morse 's code to create x and the result:

> v1 v2 v3 v4 v5 (x x[,apply(x,2,function(x) any(x=="MD"))]

v2 v3 v4

[1,] "alpha" "alpha" "MD"

[2,] "MD" "rho" "MD"

[3,] "delta" "iota" "MD"

[4,] "beta" "omega" "MD"

[5,] "mu" "MD" "theta"

Again, I am not saying this is simpler or not than your approach (or calling R from SPSS).

Bruce Weaver , I just realized that you probably meant Daniel Mello rather than Daniel Me. Oh well!

Sorry Daniel Wright, yes, I was addressing Daniel Mello. I've edited that post to clarify.

Daniel Mello, was my second guess about what you want to do correct? If so, here is a (slightly) modified version of the code that will make it a lot easier to deal with a large number of variables. (Look for the keyword TO in the AGGREGATE command.) HTH.

* --- start of syntax ---.

* Generate a small dataset to illustrate.

NEW FILE.

DATASET CLOSE ALL.

DATA LIST LIST / v1 to v3 (3A2).

BEGIN DATA

aa MD cc

dd ee ff

gg hh ii

jj MD kk

END DATA.

DATASET NAME original.

LIST.

MISSING VALUES v1 to v3 ("MD").

* Use AGGREGATE to get the number of missing values for each variable.

DATASET DECLARE Agg.

AGGREGATE

/OUTFILE='Agg'

/BREAK=

/v1 to v3 = NUMISS(v1 to v3).

DATASET ACTIVATE Agg.

* Now transose the file to get the original variable names as values of a new variable.

FLIP VARIABLES=v1 TO v3.

DATASET NAME Flip WINDOW=FRONT.

DATASET CLOSE Agg.

RENAME VARIABLES (CASE_LBL=VarName).

RECODE var001 (0=1) (ELSE=0) INTO CompleteData.

FORMATS CompleteData(F1).

* List variable names for variables with no missing data.

TEMPORARY.

SELECT IF CompleteData.

LIST VarName CompleteData.

* List variable names for variables with some missing data.

TEMPORARY.

SELECT IF NOT CompleteData.

LIST VarName CompleteData.

* Optionally, keep only variable names for variables with compete data.

SELECT IF CompleteData.

LIST VarName CompleteData.

* --- end of syntax ---.