Variable/Value Labels: SPSS Syntax with Excel

​By Becky Bell, Assistant Director for Institutional Research, Howard Community College

Including labels in data sets for both the variables and their values is often extremely important so that others can understand and correctly interpret the data as well as for producing easy-to-read infographics. However, writing out all the labels can be a lengthy task, especially for a large file or one with many value labels. For files that have a required or predetermined file structure and value codes already typed out and available in some type of file or webpage, such as state mandated student record files, national initiative data submissions or even a survey response data file, using Excel can speed up the creation of SPSS syntax (or potentially other programs) to add labels to the data file.

This method works best when both the variable names and labels are provided in a format that can be copied and pasted as text into Excel. There is always cleanup that will need to be done, and some may take more time than it’s worth, so use your judgment.

In this example, I am using the publically available file structure and data definitions for the “Term File” used by Achieving the Dream (a comprehensive non-governmental reform movement for student success) in the Community section of the Data Cookbook (a fee-based product from IData, Inc). This page provides the file’s variable names (listed next to “Ref:”) and the labels (listed on left side under “Name”).   

I highlighted and copied all of the text - in this case it was under the Data Definitions, which included the variable names and labels. 

MarTechTip2018-number 1.PNG
Then I pasted them as text into Excel:

After the text with the variable names and labels are pasted in Excel, the next step is cleaning up the text by removing all other text. Deleting rows and using Find and Replace but leaving the Replace field blank can help save time. Then cut and paste each variable label in column B next to its associated variable name (column A).

Once you have a clean list as in the example above, use an Excel formula to create the correct SPSS syntax formula (i.e. varname ‘variable label’) in column C. Using the & sign is helpful here to easily concatenate cells with additional text (in double quotes) like the space and single quotation marks around the variable label. 

This example uses the formula: = A2 & double-quote space single-quote double-quote & B2 & double-quote single-quote double-quote

An alternative is to add columns with a single space and a single quote, like this: 

Note: In Excel, you must use two single quotes in the cell in order for one to show.

Next, select, copy, and paste the new syntax created in Excel into an SPSS syntax file. Be sure to add the SPSS command VARIABLE LABELS at the top and a period at the end to complete the syntax.

This concept can also be used for creating Value Label syntax, which are often already listed in a column in the format shown here:

The easiest way to create two columns - one with the value and one with the label – is to Find “ = “ and Replace with “;”.  Then with the column selected, click on Text to Columns in Data Tools, select Delimited, click Next, select Semicolon, and click Finish.

Once formatted correctly using the same or similar Excel formula as the Variable Labels, you can create the SPSS syntax to use with the Value Labels command.

A final note: The formula for string values needs to include quotes for both the values and labels (ex. =”’”&A1&”’ ‘”&B1&”’”).​​​​



To add a comment, Sign In
There are no comments.