SPSS Tutorials: Sorting Data

Sorting data allows us to re-organize the data in ascending or descending order with respect to a specific variable. Some procedures in SPSS require that your data be sorted in a certain way before the procedure will execute. There are two options for sorting data:

1. Sort Cases (i.e., row sort)
2. Sort Variables (i.e., column sort)

We cover how to perform each sorting option below.

Sorting cases will rearrange the rows based on a given variable (or variables). The values for the selected variables can be sorted in ascending (smallest to largest, or alphabetical) or descending order (largest to smallest, or reverse alphabetical).

Once you sort the cases of a dataset, it is not possible to "un-sort" the data to its original order. If the original order of your rows is important, make sure you have a variable of that specifically and uniquely identifies the correct order of the cases first! That way, you can return to the original row order by sorting on the "order identifier" variable.

If you do not have an "order ID" variable in your dataset, a convenient way to generate one is to use the system variable $CASENUM. You can use the Compute Variables procedure (simply enter $CASENUM in the Numeric Expression box), or by running the following syntax after all of your data has been entered:

COMPUTE id=$CASENUM.
EXECUTE.

Sorting from the Data View

In the Data View, you can quickly sort your data with respect to a single variable by right-clicking on the variable name and selecting Sort Ascending or Sort Descending.

Sorting with the Sort Cases procedure

If you want to sort your data with respect to two or more variables, or if you want to have the sorted data written to a new file, you'll want to use the Sort Cases procedure:

1. Click Data > Sort Cases.

2. Double-click on the variable(s) you want to sort your data by to move them to the Sort by box. If you are sorting by two or more variables, then the order that the variables appear in the "Sort by" list matters. You can click and drag the variables to reorder them within the Sort by box.

3. In the Sort Order area, you can choose an “Ascending” or “Descending” sort order for each variable in the "Sort by" list. Click on the variable in the Sort by box to highlight it, then click the radio button that corresponds to your sort order choice.

   

   In this example, the data will first be sorted by class rank in ascending order, and will then by sorted by birthdates in descending order. That is, the birthdates will be sorted within each category of class rank. Notice that an “(A)” appears to the right of variables that will be sorted in ascending order, and a “(D)” appears to the right of variables that will be sorted in descending order.

4. (Optional) If you wish to save your newly sorted data to a new file, select the Save file with sorted data check box in the Save Sorted Data area. Then click File to specify a name and location for the new dataset file.

5. When you are finished, click OK.

You can check that your cases were sorted correctly by visually inspecting the data in Data View. In this example, cases have been sorted according to class rank, and then sorted by most to least recent birthdate.

Note: SPSS considers missing values the "smallest" value, so they will appear first if sorting in ascending order, and will appear last if sorting in descending order.

Sorting with Syntax

This syntax performs the same sort shown in the screencap above (sort by ascending class rank, then descending birth date).

SORT CASES BY Rank(A) bday(D).

Sorting variables will rearrange the order of the variables (columns) in your data. Variables can be sorted on only one attribute at a time: Name, Type, Width, Decimals, Label, Values, Missing, Columns, Align, Measure, or a custom attribute. Variables can be sorted in ascending or descending order with respect to the selected attribute.

To sort variables, follow these steps:

1. Click Data > Sort Variables.

2. The Variable View Columns list includes the attributes of variables that may be used to sort variables, including: name, type, width, decimals, label, values, missing, columns, align, measure. Select an attribute by clicking it in the list, which will highlight your selection. Note that you can only select one variable attribute. In this example, “Name” has been selected, which means that variables will be sorted according to their names.

   

3. In the Sort Order area, variables can be sorted in “Ascending” or “Descending” order. Click the radio button that corresponds to your choice. In this example, variables will be sorted in ascending order.

   You may also choose to save the current (pre-sorted) variable order in a new attribute by selecting the Attribute name check box and typing a name into the text field. Custom attributes are simply user-defined characteristics of variables that can be used to sort variables.

4. When you are finished, click OK.

Now your variables will be sorted according to the attribute you selected. In this example, the variables are sorted in ascending order according to their names (i.e., alphabetically).

Syntax

SORT VARIABLES BY NAME (A).