I am flipping my survey data so I can use it in Tableau. Here is example data in SPSS (keep in mind that each variable has value & variable labels).
ID age rate1 rate2 rate3 mr_1 mr_2 mr_3 ...
1 35 8 3 2 1 2
2 40 2 2 3 2
3 41 6 3 5 2 3
4 43 3 3 1
Where rate1-3 are 3 rating questions. Mr_1 to mr_3 is a multiple response check all the apply question (What is your ethnicity? 1=White 2=Hispanic, 3=Black)
I flip the data using this:
VARSTOCASES
/MAKE answer FROM age rate1 rate2 rate3 mr_1 mr_2 mr_3
/INDEX=Index1(7)
/KEEP= All
/NULL=KEEP.
Results look like this:
ID Index1 answer
1 1 35
1 2 8
1 3 3
1 4 2
1 5 1
...
...
...
Which works just fine when connecting this to Tableau. However, what I want is more than just Index1 as an identifier to each variable that has been flipped. What I want is this (Var, VarLab, ValueLabel are just String variables):
ID Var VarLab answer ValueLabel
1 'age' 'What is your age?' 35 '35'
1 'rate1' 'Rate food' 8 '8'
1 'rate2' 'Rate wait time' 3 '3'
1 'rate3' 'Rate bathroom' 2 '2'
1 'mr_1' 'Ethnicity' 1 'White'
1 'mr_2' 'Ethnicity' 2 'Hispanic'
...
...
...
As you can see, I retained the variable label, value label, and the variable name itself for each flipped variable. This is the ideal Tableau setup as Tableau requires "tall" datasets. Also, I can use either the string or numeric representation of the response. Lastly, I no longer need to edit aliases inside of Tableau. Any ideas how to accomplish this? Perhaps this will require python or macro? Any ideas are greatly appreciated.
Thanks!
you need to use OMS to read the dictionary into two datasets - one for variable labels and one for value labels. then you can match your restructured dataset to the variable labels by variable name, and then match it to the value labels by variable name and value.
Run this to get the two datasets - BEFORE you restructure, of course:
DATASET DECLARE varlab.
OMS /SELECT TABLES /IF COMMANDS=['File Information'] SUBTYPES=['Variable Information']
/DESTINATION FORMAT=SAV OUTFILE='varlab' VIEWER=YES.
DATASET DECLARE vallab.
OMS /SELECT TABLES /IF COMMANDS=['File Information'] SUBTYPES=['Variable Values']
/DESTINATION FORMAT=SAV OUTFILE='vallab' VIEWER=YES.
display dictionary.
omsend.
now restructure and match files - (after renaming the proper variables for matching in the two new datasets).