All people at some point will need to deal with a bad data format or tidy messy data. You may need to combine mutually exclusive data from across variables into a single variable to analyze. For example, you could be working with a tracking study and a variable with data from one wave needs to be merged with data from the next wave. This article describes how to merge variables with mutually exclusive data into a single variable based on the Structure of the variables.
Requirements
- Familiarity with the Structure and Value Attributes of Variable Sets.
- A data set containing two or more variables or variable sets that you want to combine that has mutually exclusive data (except if using Binary-Multi sets), such as in the picture below:
- If using a Binary-Multi set, variable labels will be matched and those matches merged into a single variable for each label in the final Binary-Multi set. The data does not need to be mutually exclusive. If the variables have data in multiple sets, the merge will essentially make a NET (i.e., the final value will be the max value of the matched variables, so if any of them have a 1 value, the final value will be 1).
Method - Nominal or binary-multi variable sets
- Select the Nominal variables or Binary-Multi variable sets (see data requirements above) from the Data Sources tree that you want to merge. Hold down your Ctrl key to select multiple variables.
- With all variables selected, right-click and select Combine > As Variable.
- At the next prompt, choose whether or not you want to hide the input variables. Select Yes if you no longer need to use the original variables and do not want them to appear in any of the variable drop-down lists. Otherwise, select No. A new merged variable is added to the Data Sources tree.
- In cases where more than 1 variable being combined has a value, the new combined variable will be coded as Both Input Questions Have Data. You can drag the new variable set to a Page or the Report tree to see the Counts for this category in case you need to revise the data being merged.
- OPTIONAL: To rename the new variable set, right-click on the variable in the Data Sources tree and select Rename.
Method - Numeric-multi variable sets
- You will need to temporarily make each variable set a Binary-Multi set so the automation will run. For each, select it in the Data Sources tree, and in the Object Inspector
, change Structure > Binary-Multi (don't worry about fixing the counted values).
- Select all of the variable sets that are now Binary-Multi and right-click> Combine > As Variable.
- At the next prompt, choose whether or not you want to hide the input variables. Select Yes if you no longer need to use the original variables and do not want them to appear in any of the variable drop-down lists. Otherwise, select No. A new merged variable is added to the Data Sources tree.
- Now you can change the sets back to their normal Structure. Select all of the variable sets, including the final result, and in the object inspector
Method - Numeric
The combine > as variable feature does not work with numeric variables, but you can combine the numbers mathematically. If the data is mutually exclusive, either the Sum or Maximum calculation will combine the data as desired.
- Select the Numeric variables from the Data Sources tree that you want to merge.
- From the toolbar, select Calculation
> Sum > Sum.
- A new sum variable is created that automatically ignores the missing data and sums the values across the variables for each case.
Method - Text
- Select any variable in the Data Souces tree, and click + > Custom Code > JavaScript - Text.
- The Data Editor will open at the top. You can combine the text data by referencing all of the variables separated by
+. You can drag a variable at a time to the Code Editor to have its name automatically inserted. Your final code should look similar tovar4 + var5
Next
How to Merge/Combine Categories in Tables and Charts
How to Combine Multiple Categories into a New Category