Using Excel to Write SPSS Syntax

It happens quite often that you’ve got a metadata (data describing the data, like labels, bounds for categorization, etc.) in a structured form in Excel. Suppose, you need to write a syntax which exploits this metadata in its specification. I.e., you need to “wrap” Excel file contents into SPSS syntax. Here are some cases of this kind:

• Excel contains list of variable names and list of variable labels. Need to create syntax to assign labels to corresponding variables, i.e. VARIABLE LABELS syntax;
  
• Excel contains old and new variable names. Need to create syntax to rename variables in a dataset;
  
• Excel contains values of a categorical variable and value labels. Need to assign labels to values via syntax (VALUE LABELS command);
  
• Excel contains list of ranges (boundaries) to categorize some continuous variable, corresponding categories’ values, and labels. Need to develop a syntax to compute and describe new categorical variable based on specified ranges.
  

How can one solve these tasks? The first impulse might be simply to paste data from Excel to SPSS Syntax Editor and “convert” it to the syntax manually. But with numerous variables, values or rules to process, it might be tedious to wrap it manually line by line with appropriate formatting (slashes, quotes, instructions and keywords) to get the desired valid syntax.

Let’s say FIPS codes are stored in county variable in SPSS. It would be a string variable, as we need to keep leading zeros. Here how the target SPSS syntax should look like:

To quickly create the “core” of this syntax, let’s place to the next column (D) in Excel the following formula:

(you may notice at the screenshot below the name of this function in Russian  =СЦЕПИТЬ()as I didn’t find how to avoid this in localized Excel interface)

The meaning of this formula is to create a new string, by concatenation of the following pieces:

• 4 spaces (this is not really necessary, just for visual indent to make syntax look prettier).
  
• Single quote. Note that spaces and the quote are all quoted in double quotes to ensure Excel treat them as a string value.
  
• Code value from B2 cell.
  
• Single quote, then space, and another single quote. To SPSS that will mean as closing quotation of code value and starting quotation of the corresponding county name.
  
• The content of the C2 value (county name).
  
• Finally, another single quote, to end the specification of the label.
  

Note that we separate concatenated portions with semicolon, according to Excel functions’ arguments specification.

As we finished with specification of the formula in D2 cell, the following result appears:

We need now to drag formula to the last used row in Excel sheet. This could be done quickly, regardless of how many such labels or names we have. Excel will automatically substitute cell references in the formula while coping it. The result will be like this:

Now copy all the created rows from column D (starting with the second row), and paste them into SPSS Syntax Editor.

Prepend pasted code with VALUE LABELS county string (ensure you don’t have extra line between VALUE LABELS county and lines with codes.

After running this command you may check the values were correctly assigned to the target variable:

That’s it.

To review:

1. We have started with some metadata, organized in a structured way in Excel.
   
2. We have demonstrated how to create valid syntax from this metadata with help of Excel functions (here we used =CONCATENATE(), but you may encounter situations where it might be useful to utilize other functions as well)
   
3. There are 3 steps to reach it: 1 – enter formula to create syntax portion from one row, 2 – drag the formula to create syntax for remaining rows, and 3 – paste the resulting rows to SPSS, optionally prepending and/or postpending them to make SPSS command syntax finished.