We use SPSS to analyze data and one very common format in which you will find data
is Microsoft Excel. Perhaps you did a survey in Qualtrics or SurveyMonkey and you downloaded
the results as a .csv, or perhaps, like today, your professor gives you some data in an Excel
workbook. Fortunately for us it is easy to import and clean our excel data in IBM SPSS Statistics.
First, I should tell you where you can find these data that I'm using. In the description
for each of the videos in this series you will find a link to a Google drive folder.
Right-click on any data set to download it to your computer.
We will start with the data set DogToys.xlsx Excel workbook. We will put our data on our
computer desktop. Let's look at it first in Excel. Double-click to open the file.
For the hypothetical dog toys data, we surveyed 50 dogs about the greatest thing in the world:
TOYS! We asked them how many toys do you own.
We also asked them to choose their favorite toy from a list of five. Did they prefer the
stuffed monkey, or the chew toy, or the rope bone, or the chirpy bird?
We also noted the relative size of the dogs: small, medium, or large. And finally, we made
note of what breed of dog we were surveying, grouping them as fuzzy dogs, retrievers,
and chihuahuas. "I love you, little buddy."
Then, as a thank you for answering our survey like a good dog, we offered them one of three gifts: a
hedgehog, a duck, or lamb chop. And then we kept track of how long it took for the dog to
chew the stuffing out of that gift toy. The last variable is recorded as the days
to failure of the toy. Some things to note about this data set: we have six columns,
each with a variable name on the top row. Some of the data are numbers and others are words.
Some of these words have additional spaces in front of them, which will cause a problem with
SPSS reading them as different categories. The "Toy Chosen" variable name has a space in its
name, and you remember that SPSS does not allow spaces in variable names. And the number of dog
toys owned is numeric, but we have one missing value that has been recorded as N/A,
and that is a problem because mixing numbers and letters makes this a string variable, when we
want to use it as numeric. Well, good news... all of these potential problems can be fixed!
So for now, close the data set in Excel. It is important that the data not be open
in another program while we are trying to import it into SPSS. Now open SPSS.
Ignore this untitled data set. We begin with the FILE command. Note that we can create new
data sets, as well as syntax files, output, or Python 3 scripts. Opening data in SPSS
can be accomplished using command File -> Open. Use this option to open SPSS data files, syntax,
output files, and scripts. But the technique that I'm going to show you works for importing
not only Excel files, but also the common .csv or text formats, plus data sets from
various other popular statistical software packages ,or to query a database directly.
Go to File -> Import data -> (and as you might guess) Excel. We put our data set on the desktop.
Click on the name of the file and then click Open. The data wizard will open
showing us the default settings for importing our data. You may recall that we wisely put the names
of our variables on the first row of our data set. SPSS will import them as variable names.
SPSS will also examine our data and make a determination of their level of measurement.
Our numeric variables will be imported as scale and the variables with words will be imported as
string variables, which are categorical data, and nominal level. Remember that we had one numeric
variable with a single missing value that was coded as N/A. Having even a single non-numeric
entry is enough to cause SPSS to import this numeric variable as a string variable.
We don't want that. We want this to be numeric; however, because at least 95 percent of the
values are numeric, SPSS will import this variable as numeric and simply remove the N/A.
If we had hidden rows or columns in our data set, they would be ignored. Remember that
some of the breed names had extra spaces in front? Watch the "Dog Breed" variable
when we remove those pesky leading spaces... and trailing spaces from our string variables.
I always choose this setting just to be safe. Now, click OK.
We see that "ToyChosen" imported without a space in the variable name,
but SPSS kept the original variable name as a variable label. We also see
that instead of importing the numeric variables as scale, as promised, SPSS has imported them
as nominal variables. This is very easy to fix. Change the first and the last variables to scale.
Relative size (small, medium, large) tells us that the dog sizes are different,
but not by how much, so it is ordinal. We now have two numeric scale variables and
four nominal level string variables, one of which is actually ordinal.
And I always encourage researchers to include variable labels and value labels in their data
set. Although the meaning of the variables is clear right now, if you return to this data set
in a few weeks or months, you may have trouble remembering what each variable name stood for.
Add those variable labels now and Future You will thank Present You for being so conscientious.
"Number of dog toys owned" "Favorite toy of five"
"Breed of dog"
"Relative size of dog", "Toy chosen as a reward"
"Days until gift toy was chewed up". At this point, we should save our data set.
Go to File -> Save as... and save the data set as "Dog Toys".
Save it to the desktop and click Save. It is now saved as an SPSS file (.SAV).
In Data View, the dog size and toy chosen columns are
a little too narrow. That is simple enough to widen.
We have only one missing data point. The N/A was removed from "Toys owned"
and left as a system missing value. Oh wait, I just found the original survey and discovered that
this particular dog actually owned three dog toys... we can add that data point now (3).
So far, so good. But this data could benefit from a number of improvements. The first thing
that I would like to do is make sure that every participant in this study has a unique identifier:
a number that can be used to identify a case even if the data are sorted to change their order.
To add an identifier, go to Transform -> Compute Variable. We will create a brand new variable
using a numeric expression built into SPSS. First, we need a name for our variable. We will call it
"Dog". We can even add a label by clicking on "Type and Label." We will label it "Unique
identification number," and although we don't need it for this example, if you are creating a
new variable from a mathematical formula, such as using height and weight to calculate BMI,
you could use that mathematical formula - called an expression - as the variable label. Very handy!
Click Continue. For our numeric expression, we will use one of the functions that is
included with SPSS. The simplest way to find the case number function is to click
on the function group "All". It happens to be the first available function CASENUM. When you click
once on the function itself, you will see information about the function, and what it does.
Information is available for all functions, which can be very helpful in interpreting or using a
function. Using the CASENUM function is as simple as dragging it to the numeric expressions box. By
default the function will begin numbering at 1. We could add a constant, such as 100, so that the
identifiers begin at 101. Particularly useful if you are numbering Dalmatians. Now click OK.
Double-click on the variable name to see it in Variable View. Scroll up to see the rest. Set the
decimals to 0, and the measure to nominal. Because this is a naming variable, rather than a scale,
I would also like this variable to be the first in my data set. So I will click on it
and drag it to the top of the variable list.
Before I show you this next trick, let me illustrate
why it is necessary. Go to Analyze -> Descriptive Statistics -> Frequencies.
Move the string variable "Favorite Toy" and "Breed" into the variables box, and click
on Charts. Choose bar chart, Continue, and OK. We get a frequency table for each toy and dog type,
and we also get a bar chart. Great! Now go to Analyze -> Descriptive Statistics ->Descriptives.
Notice that this time our string variables do not show up. Click Cancel.
How about Analyze -> Compare Means -> One-way ANOVA. Again, no string variables.
Many of the analyses in SPSS require numeric variables, so it would be much
more convenient if we could recode our string variables into numeric variables,
and set the words as variable labels. And, of course, we don't want to do all of that work
recoding each and every label by hand, so go to Transform -> Automatic Recode.
We would like to recode the string variables for "Dog Breed", so move it into the Variable box.
Next, we need a new name for this variable that we are creating. We will call it "Breed".
Add the new name. Breed will be treated as a factor. A factor is another name for a categorical
variable that you use as a grouping variable or an independent variable in an analysis.
I guess while we're at it, we should recode the other three string variables, as well.
All of the other settings are good at their defaults, so when you are ready, click OK.
We can now see the new variables, each with their own label, same as the existing label. Each is set
to nominal level of measurement and now with value labels added for each numeric value:
1 = Chihuahua, 2 = Fuzzy Dog, and 3 = Retriever.
Click over to Data View. We see our new variables, but they still look just like the
original string variables, except that the words are right justified instead of left justified
,and there is no "a" on the green ball of the nominal variable; however, SPSS has transformed
this string variable into numeric. To prove this to yourself, locate the icon for "Value Labels."
Toggle this icon to switch between showing numbers, and the corresponding value labels.
Because we created new variables, we now have variables in our data set that we will not use.
One option, obviously, is to delete the original string variables.
But we have another option for when we do not want to be rid of the variables,
but instead we want to simply hide the variables, without actually deleting them.
Go to Utilities -> Define Variable Sets. We will call our new set of variables "Dog toy analysis."
We want to choose our variables, but right now,
we are seeing the variable labels instead of the variable names.
This next trick works in any dialog box. Right-click (or
control-click on Mac) and select any variable, and then choose "Display variable names."
For our variables, we want to include the identifier,
toys owned, days to fail, and the four new factor variables.
I'm double-clicking to move these variables into the data set. Now click on "Add Set."
Note that we can easily toggle back to the "Display variable labels" at any time. And close.
Now, go to Utilities -> Use variable sets. You can see that we have options for all variables
and new variables. We will uncheck them all and then check just the analysis set that we created.
Click OK. We now have only selected variables to work with; however, if we wanted to see the
hidden variables, we could return to the Utilities menu and "Show all variables."
We use SPSS to analyze data. And now we are ready to do some data analysis. Let's review what you've
learned so far: you now know how to import data from Microsoft Excel, including a .csv, or text,
or other data format, plus how to tweak the settings to make the import cleaner.
You reviewed how to change levels of measurement and other settings for your variables.
You created a unique identifier for each participant. You automatically recoded string
variables into a more usable nominal variable numeric format. You can show variable labels and
variable names, both in the data set and in the analysis windows, and you learned how to create
variable sets to manage your data. Next time, we will turn to showing descriptive statistics,
first for categorical variables and later for scale numeric variables.