视情况而定保留Tableau设置的变量/值标签-重组Tableau的数据，翻转数据 [英] Vars to cases & retain variable/value labels Tableau setup - restructure data for Tableau, flip data

问题描述

我正在翻转调查数据，以便可以在Tableau中使用它。这是SPSS中的示例数据（请记住，每个变量都有值和变量标签）。

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         

其中费率1-3是3个评分问题。 Mr_1至mr_3是一个多人回答，请检查所有适用的问题（您的种族是什么？1 =白人2 =西班牙裔，3 =黑人）

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)

我使用此翻转数据：

VARSTOCASES
  /MAKE answer FROM age rate1 rate2 rate3 mr_1 mr_2 mr_3
  /INDEX=Index1(7)
  /KEEP= All
  /NULL=KEEP.

结果如下：

ID Index1 answer
1    1      35
1    2      8
1    3      3
1    4      2
1    5      1
...
...
...

工作正常将其连接到Tableau时。但是，我想要的不仅仅是索引1作为已翻转的每个变量的标识符。我想要的是什么（Var，VarLab，ValueLabel只是字符串变量）：

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

如您所见，我保留了每个翻转变量的变量标签，值标签和变量名称本身。这是理想的Tableau设置，因为Tableau需要较高的数据集。另外，我可以使用响应的字符串或数字表示形式。最后，我不再需要在Tableau中编辑别名。任何想法如何做到这一点？也许这将需要python或宏？

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.

谢谢！

推荐答案

您需要使用OMS将字典读入两个数据集-一个用于变量标签，一个用于值标签。
，则可以按变量名称将重组后的数据集与变量标签匹配，然后按变量名称和值将其与值标签匹配。

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

这篇关于视情况而定保留Tableau设置的变量/值标签-重组Tableau的数据，翻转数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！