Blanks in the data – how to find them: Blanks in a data set are often

Blanks in the data – how to find them:

Blanks in a data set are often used to represent missing data. This is not allowed

with free format data where it causes the data to be misread. Other missing

value flags must be used. Blanks can be used as missing value flags only for fixed

format data where the FORMAT option is used to specify the format of the data

set and MISSING = BLANKS; is used to specify that blanks are the missing value

flag for the data set. This FAQ shows how to find blanks in a data set using Excel.

1. Import your data into Microsoft Excel using the File->Open command.

2. In the “Import Text Wizard”, choose “Delimited” under Original data type,

and press Next:

3. Select “Tab”, “Comma”, and “Space” from the Delimiters options, and

select “Treat consecutive delimiters as one”, and press Finish:

4. Once you have imported your data you can use the following methods to

locate the blank values:

METHOD 1:

1. Press F5, then select “Special” from the lower left-hand corner.

2. In the “Go To Special” dialog box, select “Blanks” and press OK. This will

highlight all blank cells in the data, which you can scroll to find.

METHOD 2:

1. Press Ctrl-F, do NOT enter any information into the text box (no spaces, just

empty) and press “Find Next”. This will jump you to the next blank in the

data.