While working in SPSS, it's pretty common to reorder your variables. This tutorial shows how to do so the right way. We encourage you try the examples for yourself by downloading and opening hotel_evaluation.sav, a screenshot of which is shown below.
SPSS Reorder Variables Example 1
Right, the most common way for reordering variables in SPSS is by running ADD FILES. Before explaining how it works, let's first show that it works in the first place. So suppose we'd like to swap the variables fname and sex. Running the syntax below does just that.
add files file *
/keep id sex fname all.
execute.
Result
How Does It Work?
Remember that ADD FILES
merges data sources holding different cases but similar variables. Note that in SPSS syntax, an * addresses the active dataset (often the only data that's open in SPSS).
Now, if we specify this as the only data source, it gets merged with nothing. This means that running
add files file *.
does absolutely nothing whatsoever. However, this command becomes useful if we add a /keep
subcommand which tells SPSS which variables to keep (not delete) and in which order.
In our first example, we decided to keep id sex fname
. Note that the original order here was id fname sex
.
Finally, all
is a special keyword that addresses all other variables in their original order. Omitting all
deletes these variables from our data. The final result of all this is that we basically do nothing except keep all variables in a different order than previously, which is exactly what we were intending.
SPSS Reorder Variables Example 2
Right, we just swapped two variables in our data. But what about moving entire blocks of variables? For instance, suppose we want to have variables q1 through q5 right after fname in our data. Well, this doesn't pose any challenge whatsoever if we use SPSS’ TO keyword. The syntax below shows just how easily it's done.
add files file */keep id to fname q1 to q5 all.
execute.
Result
SPSS Reorder Variables Example 3
Recap: if we're having some data open in SPSS, we can easily reorder our variables by using an ADD FILES command with a keep
subcommand.
Is that the only way to get it done? Well, no. If we're done with a dataset, we may use the exact same keep
subcommand in a save
command as shown below (first example). This is never really necessary but it may save a tiny amount of syntax and processing time.
Just for the sake of completeness, if we know in advance which variables are present in our data, we may also reorder them while opening the data file with get
.
SPSS Reorder Variables Syntax Examples
save outfile '10_all_data_prepared.sav'
/keep q1 to q5 all.
*2. Reorder variables when opening data file.
get file 'hotel_evaluation.sav'
/keep q1 to q5 all.
SPSS SORT VARIABLES
There's just one more thing that we need to mention: SPSS (starting from version 16) also has a SORT VARIABLES
command. Like so you can sort variables according to variable name, variable type, variable label, format or a couple of other properties.
We obviously needed to mention this command here. However, we very rarely use it in practice. The reason is that the aforementioned sorting options virtually never correspond to the order we desire for our variables. Those who like to give it a quick try anyway, may run something like
sort variables by name.
SPSS Reorder Variables - Final Note
In some cases, you may need to sort your variables in a structured manner that's more complex than covered by this tutorial. An example is sorting (many) variables according to subscripts in their variable names. The way to get such cases done efficiently is by using Python in SPSS.
THIS TUTORIAL HAS 22 COMMENTS:
By Ruben Geert van den Berg on May 27th, 2021
Hi Roman!
ADD FILES is meant for "adding files": it merges data files with similar variables but different cases. It's not at all meant for reordering variables in a single file. My method comes down to "abusing" this command.
But if it works, it works.
P.s. you could pull the exact same trick with MATCH FILES instead (meant for adding files with similar cases but different variables). My only reason for preferring "ADD FILES" is that it is 2 characters shorter...
By Roman on May 28th, 2021
Thank you for the explanation and the workaround, Ruben!
Your invetiveness in 'abusing' existing functions, covering non-existent ones, saved me a lot of time.
I just was surprised that there is no native method to do custom sorting with SORT.
By Ruben Geert van den Berg on May 29th, 2021
At your service.
Note that there is a SORT VARIABLES command and it does come in handy sometimes -especially for "unmixing" string variables and numeric variables.
Last, ADD FILES is a command ("stand alone instruction"), not a function (modifier that's part of a command).
Kind regards,
Ruben
SPSS tutorials
By Michelle on August 30th, 2022
I have over 100 variables that I am renaming. Is it possible to re-order the variables in the order they are renamed? This is needed because I am comparing then combining datasets and the variables are in a different order for each of the dataset. I am using the rename variable syntax.
By Ruben Geert van den Berg on August 30th, 2022
Hi Michelle,
It's often easier to compare data after merging them.
If it's a vertical merge (ADD FILES instead of MATCH FILES), SPSS takes care of the order: the merged file follows the order of the file you specify first.
However, you should make sure that the dictionary information (variable/value labels and so on) is identical over files before merging them.
I often do this by running
DISPLAY DICTIONARY.
on both files, copy-pasting the results in Excel (optionally sorting these by variable names) and then comparing results with some Excel formulas.
Alternatively, run
SORT VARIABLES BY NAME.
on both files before comparing them.
Hope that helps!
SPSS tutorials