SPSS tutorials

BASICS DATA ANALYSIS T-TEST ANOVA CHI-SQUARE TEST

Apply Dictionary Information from Excel

Question

“I have an Excel workbook whose three sheets contain data values, variable labels and value labels. How can I apply the dictionary information from these last two sheets to the SPSS dataset after importing the data values?”

Option A: Python

A nice and clean option is to have Python read the dictionary information from the Excel sheets. The cell contents can then be inserted into standard `VARIABLE LABELS` and `ADD VALUE LABELS` commands. Running these commands applies the variable labels and value labels to the data values. We'll use data_and_labels.xls for demonstrating this approach.

Reading Excel data values into SPSS is straightforward. We usually paste the required syntax from File Open Data. The screenshot below shows which options to select.

Importing Excel Data into SPSS

SPSS Syntax for Reading Excel Data

*1. Read data values (pasted syntax from GUI).

GET DATA
/TYPE=XLS
/SHEET=name 'data'
/CELLRANGE=full
/ASSUMEDSTRWIDTH=32767.

2. Create Variable Labels Command

Let's first open our workbook and take a look at how the second sheet is structured. As shown in the screenshot below, the first column holds variable names and the second variable labels.

SPSS Variable Labels in Excel

Now we'll read this second sheet with Python instead of SPSS. Note that you need to have the SPSS Python Essentials as well as the xlrd module installed first. The syntax below shows how to create the `VARIABLE LABELS` commands as a single (multi line) string. For now we'll just print it for inspection.

SPSS Python Syntax Example

*2. Create and inspect VARIABLE LABELS commands.

begin program.
import xlrd
varLabCmd = ''
wb = xlrd.open_workbook(xlsPath)
varLabs = wb.sheets()[1]
for rowCnt in range(varLabs.nrows):
rowVals = varLabs.row_values(rowCnt)
varLabCmd += "variable labels %s '%s'.\n"%(rowVals[0],rowVals[1].replace("'","''"))
print varLabCmd
end program.

3. Create Value Labels Command

SPSS Value Labels in Excel

Remember that Python objects persist over program blocks. We can therefore leave out the first lines of syntax from the previous example. The Excel sheet holding value labels has the same basic structure as the one with variable labels (see screenshot). The main difference is that we'll now insert three pieces of information (variable name, value, value label) into each line. We'll generate our `ADD VALUE LABELS` commands as shown below.

SPSS Python Syntax Example

*3. Create and inspect ADD VALUE LABELS commands.

begin program.
valLabCmd = ''
valLabs = wb.sheets()[2]
for rowCnt in range(valLabs.nrows):
rowVals = valLabs.row_values(rowCnt)
valLabCmd += "add value labels %s %d '%s'.\n"%(rowVals[0],rowVals[1],rowVals[2].replace("'","''"))
print valLabCmd
end program.

Running the Python Generated Syntax

If neither of the generated commands require any further tweaking, the only thing left to do is just run them by using `spss.Submit`. The syntax below does so and thus finishes this job.

*4. Run both commands.

begin program.
import spss
spss.Submit(varLabCmd)
spss.Submit(valLabCmd)
end program.

Option B: Syntax Generating Syntax

Before Python was introduced to SPSS, a different approach was needed for this situation. It comes down to declaring a new (long) string variable and using CONCAT to create lines of syntax as string values. Next, we save the contents of this string variable as a .txt file with an .sps extension and INSERT it.
We don't usually recommend taking this approach but we'll present it anyway for the sake of the demonstration. Some of the commands used by the syntax below are explained in SPSS Datasets Tutorial 1 - Basics and SPSS String Variables Tutorial.

SPSS Syntax Generating Syntax

*1. Set working directory.

*2. Read data values (pasted syntax from GUI).

GET DATA
/TYPE=XLS
/FILE='data_and_labels.xls'
/SHEET=name 'data'
/CELLRANGE=full
/ASSUMEDSTRWIDTH=32767.

dataset name values.

GET DATA
/TYPE=XLS
/FILE='data_and_labels.xls'
/SHEET=name 'variablelabels'
/CELLRANGE=full
/ASSUMEDSTRWIDTH=32767.

dataset name varlabs.
dataset activate varlabs.

string syntax(a1000).

*4. Create syntax in data window.

compute syntax = concat("variable labels ",rtrim(v1),"'",rtrim(replace(v2,"'","''")),"'.").
exe.

*5. Save variable holding syntax as .sps file.

write outfile 'insert_varlabs.sps'/syntax.
exe.

dataset close varlabs.

*6. Import value labels sheet.

GET DATA
/TYPE=XLS
/FILE='data_and_labels.xls'
/SHEET=name 'valuelabels'
/CELLRANGE=full
/ASSUMEDSTRWIDTH=32767.

dataset name vallabs.
dataset activate vallabs.

string syntax(a1000).

*7. Create syntax in data window.

compute syntax = concat("add value labels ",rtrim(v1)," ",ltrim(str(v2,f3)),"'",rtrim(replace(v3,"'","''")),"'.").
exe.

*8. Save syntax variable as .sps file.

write outfile 'insert_vallabs.sps'/syntax.
exe.

dataset close vallabs.
dataset activate values.

*9. Run both syntax files.

insert file = 'insert_varlabs.sps'.
insert file = 'insert_vallabs.sps'.

*10 Optionally, delete both syntax files.

erase file = 'insert_varlabs.sps'.
erase file = 'insert_vallabs.sps'.

Let me know what you think!

*Required field. Your comment will show up after approval from a moderator.

• By Abdallah on November 10th, 2015

Dear
many thanks to you.
I was searching for this method too long time

• By Ruben Geert van den Berg on October 2nd, 2015

"Is there a way to have Python just print the entire syntax into a syntax window" => Yes, that's possible but I think it's not the best solution for your problem.

It would be way easier to have Python print the syntax to your output window by simply using Python's `print` command. Then you could copy-paste from output to syntax window and proceed.

However, the way to go is probably to make the Python generated SPSS commands conditional on the existence of the variables they address. Use Python's `replace` method for replacing special characters. This approach may take some initial effort but that's likely to pay off in the end.

• By William Dudley on October 1st, 2015

I am trying to apply variable labels and value labels using a dictionary in excel which has about 2000 lines. I am encountering errors in several ways

First this dictionary is a global dictionary and any given data file that the client exports will have just a subset of the variables that are in the dictionary so I get error (or warnings) that the Ad value command references an unknown variable. It seems like SPSS seems to tolerate this. But I am not sure

Another error has to do with special characters in the client's variable names like parentheses.

Is there a way to have Python just print the entire syntax into a syntax window so that I can run the syntax in pieces and debug?

Thanks

Bill

• By Ruben Geert van den Berg on October 1st, 2015

Thanks for pointing that out! The link to the Excel file has been fixed now.

• By William Dudley on October 1st, 2015

I appreciate this work but get stuck with the variable labels and value labels command because I cannot access the data_and_labels.xls which hold the key to the workings of these programs.