StaCrossTab.Tabulate
Unstack a spreadsheet.
| Syntax | Parameters | Return Value |
|---|---|---|
Function StaCrossTab.Tabulate( _
InSpread As Object, _
CrossTabRows As Variant, _
CrossTabColumns As Variant, _
CrossTabValues As Variant, _
Optional AggType As XTabAggregateType = 0, _
Optional MIDType As XTabMultipleIDType = 0, _
Optional Sorted As Boolean = True, _
Optional IncludeUnselected As Boolean = False, _
Optional ConcatenationString As String = "", _
Optional AggregateExpression As String = "", _
Optional selectionIncludeExpression As String = "", _
Optional selectionIncludeList As String = "", _
Optional selectionExcludeExpression As String = "", _
Optional selectionExcludeList As String = "", _
Optional maxVars As Integer = 4000) As Object
|
Input spreadsheet to unstack. Type: Object The case ID variables (optional) containing the unique case or unit IDs (for the unstacked file. Type: Variant The Code variable containing the unique variable IDs (for the unstacked file). Type: Variant The variables to unstack. Type: Variant The option to specify how to handle multiple references to the same case or unit IDs (rows) or variable IDs (columns). Type: XTabAggregateType Default value: 0 The option to customize the method to be used for aggregating the measurement or variable values. Type: XTabMultipleIDType Default value: 0 Set this to True if the input data file is already sorted into ascending order by the unique case, unit, or row IDs, and the selected variable, measurement, or column IDs. Data files that are already sorted in this manner can be processed much faster. Type: Boolean Default value: True Set this to True to also use other variables (all variables that were not explicitly selected) and "carry them along" to the unstacked new data spreadsheet. Type: Boolean Default value: False Set this to one or more characters to be used when creating concatenated variable names for the unstacked file. For example, if two variables were selected for the column, variable, or measurement IDs, and you selected as the separator an ampersand ("&"), then the new variables would consist of the unique combinations of values found in the two variables, e.g., Avalue&BValue. Type: String Default value: "" A transformation formula for combining the multiple values found in the respective variables (only a single variable will be created for each unique combination of values or codes found in the Column Variables). Use the spreadsheet formula syntax conventions and variable references to the positions and names of Value Variables as they appear in the input file (e.g., references to a variable V4 will resolve to the 4th variable or column in the input data file before the unstacking operation is applied). Type: String Default value: "" The case selection expression to specify which cases to include in the output. Type: String Default value: "" The case selection list to specify which cases to include in the output. Type: String Default value: "" The case selection expression to specify which cases to exclude in the output. Type: String Default value: "" The case selection list to specify which cases to exclude in the output. Type: String Default value: "" The maximum number of variables allowable in the output. Type: Integer Default value: 4000 |
Object |
Remarks
In general, the purpose of these options is to perform "stacking" or "unstacking" of data files to make them suitable for statistical analysis; the general operation or restructuring of the data is similar to a cross-tabulation of values (given certain categorical variables), and this type of operation is also referred to as a cross-tabulation (restructuring) of the data (even though this specific operation is not limited to frequency data, nor is that application the one most commonly encountered). These operations are typically applied when accessing data from databases, where the individual data values are identified by case and variable IDs, and each unique combination of these IDs are contained in a new row of the data file.
SVB Example
Unstacking a dataset:
Option Explicit
Option Base 1
Sub Main
Dim S1 As Spreadsheet
Set S1 = Spreadsheets.Open(Path & "\Examples\Datasets\Cat Clinic Patient Info.sta")
S1.Visible = True
Dim obj2 As New StaCrossTab
obj2.SkipMissing = True
obj2.PreCreateVariables = True
obj2.PreserveRelativeVariableOrder = True
obj2.SortOutputVariables = True
Dim S2 As Spreadsheet
'Unstack (split) the GENDER variable, so that its codes
'are now separate variables.
Set S2 = obj2.Tabulate(S1, "1", "2", "3", scXTabAggSeparateVariables, scXTabMIDFirst, False, False, "", "", "", "", "", "", 4000)
S2.Visible = True
End Sub