An often requested feature is to export variable and value labels to Excel. This handy tool creates an SPSS Dataset containing these labels. It can either be be saved as an Excel sheet or further edited in SPSS.
SPSS Create Dictionary Dataset Tool - How To Use
- Make sure you have the SPSS Python Essentials installed.
- Next, download and install the Dictionary Dataset Tool. Note that this is an SPSS custom dialog.
- Click .
- Click syntax. and run the pasted
- This creates a new dataset called Dictionary_Overview holding all value labels and variable labels.
- Note that the value for all variable labels is (the lowest value found in the dictionary -1). It merely serves as a placeholder for the value label "Variable Label" and should not be taken literally.
- To avoid confusion, display value labels rather than values by clicking the value labels icon (see screenshot below).
- Clicking the tool's button will take you to this tutorial. We very much appreciate your feedback on it.
Saving the dictionary overview as Excel sheet
Creating a single sheet Excel workbook holding the dictionary information is demonstrated below. Note that it saves value labels rather than values. For more on setting your working directory see Change Your Working Directory.
cd 'd:/temp'.
*Save as Excel sheet.
save translate outfile 'dictionary_overview.xls'
/type xls
/version 8
/fieldnames
/cells = labels.
Final Note
We've had some doubts regarding the optimal output format before we finally went with a single dataset holding all value and variable labels. An alternative we considered was to directly create an Excel workbook with separate sheets for value labels and variable labels. We may offer this as a second version at some point.
THIS TUTORIAL HAS 13 COMMENTS:
By Tolga on May 8th, 2017
Hey Ruben,
Is it possible to get the variable list in order of the original file but not alphabetically ? Do you had any time to split this in to 2 worksheets for vallabs and varlabs ? Thank you.
By Ruben Geert van den Berg on May 8th, 2017
Hi Tolga!
I kinda agree with both suggestions but I can't spend any time on this tool right now. Getting the job done is not really that hard though since it's basically just a combination of DISPLAY DICTIONARY. and OMS. Perhaps read up on the latter tutorial as it's really interesting but somewhat underrated.
Best,
Ruben
By Lori on July 3rd, 2020
Hi Ruben,
I tried installing the Dictionary Dataset Tool, but SPSS (26) told me "Error reading custom dialog specification." Should the tool work with SPSS 26? Or do you know what might be causing the problem?
By Ruben Geert van den Berg on July 4th, 2020
Hi Lori!
Try installing it via the compatibility mode as shown here.
I just did so on SPSS 26 and it worked fine. For SPSS 27, you'll run into a new problem but we'll soon discuss that separately in due time.
Hope that helps!
SPSS tutorials
By Lori on July 15th, 2020
Thanks, Ruben--but what file should I use? (The link you provided showed how to upload the file, but the file itself wasn't clear.) Thanks again, in advance--I really appreciate it!