Vars to cases & retain variable/value labels Tableau setup - restructure data for Tableau, flip data

Question

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!

Sassan

If you are using Tableau to view survey data, check out the blog datarevelations.com for several good tips – Peloquin

Answer 1

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).

Herodias

The second OMS that pops up does appear to work, it's got the value labels as a string variable now. However, the first OMS has 3 variables, Command_, Subtype_, and Label_ but the dataset is empty. I don't have the string variable of the variable label. Any ideas? Hope that makes sense. – Sassan

The variable labels are in a table with OMS subtype "Variable Information" not Variable Labels. – Tzong

Thanks for the correction @JKP. I've edited the code above, it should work for you now NickZiebert . – Herodias

Thanks, this definitely gives all the required info in order to solve the problem. I ended up using vdict[j].VariableLabel in python and compute !var = valuelabels(!varDelete). in a macro to get variable/value labels. This is a much more elegant way to get var/value labels though. – Sassan

you should post your own full solution as an answer here, for future users. – Herodias

@Herodias I'm using your solution and I'm about 98% there, much less code, but there's a problem matching by variable name and value on the vars that don't actually have a value label (when rate1 = 3 for example). If no value label, it doesn't appear is vallab OMS. – Sassan

Not sure I understand the problem - naturally only existing labels will be matched, no? If you want more labels you can either add value labels before the restructure, or - you could fill in the blanks after the process, e.g. if ValueLabel="" ValueLabel=string(answer,f3). – Herodias

@Herodias Yes, only existing labels are matching. And yes, I can just add value labels at the start, fixing the issue, but this code is meant to be automated and I won't have to worry about that. I need something that does this automatically. add value labels rating2 2 '2' 3 '3' 4 '4' ... – Sassan

my "after" option will do the job - once you're done matching the two files, fill in the blanks automatically - this is the simplest way I can think of: if ValueLabel="" ValueLabel=string(answer,f3). – Herodias

@Herodias Got it working, the steps I was taking were wrong. This is a much shorter solution...Thanks. Updated my answer. – Sassan

Answer 2

This is the solution based on the other answer using OMS, and I added few other things. This flips the vars you want and converts any other var you want to string.

dataset close all.
new file.

get file 'C:\Users\nicholas\Desktop\testFile.sav'.

************************************************************************************************ 
TABLEAU SETUP
********************************************** 

insert file="C:/Users/nicholas/Desktop/Type2syntax.sps".

!toString vars = visitorType.

!flipAndMatch vars = rate1 rate2 rate3 mr_1 mr_2 mr_3.

exe.

*CATEGORIZE FLIPPED VARS

String filter (a150).
!groupingBy 'Rating satis' rate1 rate2 rate3.
!groupingBy 'MR with' mr_1 mr_2 mr_3.

save outfile 'C:\Users\nicholas\Desktop\OtherTableauTest2.sav'.

"C:/Users/nicholas/Desktop/Type2syntax.sps" is :

* Encoding: UTF-8.

save outfile 'C:\Users\nicholas\Desktop\tempSav.sav'.

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.

DATASET ACTIVATE varlab.
rename variables var1= varName / label = Question.
alter type varName (a20).
alter type Question (a1000).
sort cases by varName.
SAVE OUTFILE='C:\Users\nicholas\Desktop\varlabsTemp.sav'
/keep varName Question.

DATASET ACTIVATE vallab.
rename variables var1=varName / var2 = AnswerNumb / Label = AnswerText.
alter type varName (a20).
alter type AnswerText (a120).
sort cases by varName AnswerNumb.
SAVE OUTFILE='C:\Users\nicholas\Desktop\vallabsTemp.sav'
/keep varName AnswerNumb AnswerText.

dataset close all.
new file.

get file  'C:\Users\nicholas\Desktop\tempSav.sav'.
compute UNIQUE_ID = $casenum.


DEFINE !toString (vars=!CMDEND)
!DO !var !IN (!vars)
!LET !varDelete=!CONCAT("Delete", !var)
rename variables !var = !varDelete.
String !var (a120).
compute !var = valuelabels(!varDelete).
exe.
delete variables !varDelete.
!DOEND
!ENDDEFINE.

DEFINE !groupingBy (!POSITIONAL !TOKENS(1)
               /!POSITIONAL !CMDEND)
!DO !var !IN (!2)
!LET !varString=!CONCAT("'", !var,"'")
if varName eq !varString filter eq !1.
!DOEND
exe.
!ENDDEFINE.

DEFINE !flipAndMatch (vars=!CMDEND)
VARSTOCASES
 /MAKE AnswerNumb FROM !vars
 /INDEX=VarName (AnswerNumb)
 /KEEP=ALL
 /NULL=KEEP.
EXECUTE.

sort cases by varName AnswerNumb.
alter type varName (a20).

match files files*
/table='C:\Users\nicholas\Desktop\vallabsTemp.sav'
/by varName AnswerNumb.

match files files*
/table='C:\Users\nicholas\Desktop\varlabsTemp.sav'
/by varName.

if AnswerText eq '' AnswerText = string(AnswerNumb, f).
!ENDDEFINE.

Output looks something like this. I didn't flip age or visitorType, but I certainly could have.

UNIQUE_ID    VarName    AnswerNumb    AnswerText   Question           filter          age       VisitorType
1             'rate1'        8           '8'      'Rate food'     'Rating group'       35    'Overnight Visitor'
1             'rate2'        3           '3'      'Rate wait time''Rating group'       35    'Overnight Visitor'
1             'rate3'        2           '2'      'Rate bathroom' 'Rating group'       35    'Overnight Visitor' 
1             'mr_1'         1         'White'    'Ethnicity'      'MR group'          35    'Overnight Visitor'
...

Sassan