StaCrossTab.Flatten

This function stacks a spreadsheet by the specified variables.

Syntax Parameters Return Value 
Function StaCrossTab.Flatten( _
    InSpread As Object, _
    Vars As Variant, _
    DestName As String, _
    CodeName As String, _
    Optional OmitMissing As Boolean = True, _
    Optional CreateCodeVariableAsText As Boolean = False, _
    Optional selectionIncludeExpression As String = "", _
    Optional selectionIncludeList As String = "", _
    Optional selectionExcludeExpression As String = "", _
    Optional selectionExcludeList As String = "", _
    Optional Interleave As Boolean = True, _
    Optional nSeries As Integer = 1, _
    Optional CopyUnstacked As Boolean = True, _
    Optional seperateOutputVariablesByType As Boolean = False, _
    Optional createOutputVariablesOfAllTypes As Boolean = False) As Object
  • InSpread [in]

The spreadsheet to stack.

Type: Object

  • Vars [in]

The variables to stack (combine).

Type: Variant

  • DestName [in]

The name of the output variable that will hold the values from the stacked variables.

Type: String

  • CodeName [in]

The name of the output variable holding the codes (i.e., the names of the stacked variables).

Type: String

  • OmitMissing [in,optional]

Whether or not cases with missing data should be removed from the output.

Type: Boolean

Default value: True

  • CreateCodeVariableAsText [in,optional ]

Whether the code variable should be a text variable (instead of using text labels).

Type: Boolean

Default value: False

  • selectionIncludeExpression [in,optional ]

The case selection expression to specify which cases to include in the output.

Type: String

Default value: ""

  • selectionIncludeList [in,optional]

The case selection list to specify which cases to include in the output.

Type: String

Default value: ""

  • selectionExcludeExpression [in,optional]

The case selection expression to specify which cases to exclude in the output.

Type: String

Default value: ""

  • selectionExcludeList [in,optional]

The case selection list to specify which cases to exclude in the output.

Type: String

Default value: ""

  • Interleave [in,optional]

Whether to stack the cases as they are ordered in the original spreadsheet. Set to False to stack the output by the groups in the Code variable.

Type: Boolean

Default value: True

  • nSeries [in,optional]

Set this to have groups of stacked output rather than a single stacked variable. The series count denotes the number of variables present in each stacking series. Variables are ordered sequentially based on the selection from the variable selection dialog. If the series count cannot evenly divide into the total number of selected variables then the last stacking group will have fewer output variables and be padded with missing data.

Type: String

Default value: ""

  • CopyUnstacked [in,optional]

Whether the unstacked variables should be included in the output.

Type: Boolean

Default value: True

  • seperateOutputVariablesByType [in,optional]

If set to True, multiple variable types will be created in the output file, one per input variable type (double, integer, byte, text, or date).

Type: Boolean

Default value: False

  • createOutputVariablesOfAllTypes [in,optional]

If set to True, all possible output variable types (double, text, integer, byte, and date) will be created in the output. Thus the number of variables and their order is predictable and consistent.

Type: Boolean

Default value: False

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

Stacking a dataset: 

Option Explicit
Option Base 1
Sub Main
    Dim S1 As Spreadsheet
    Set S1 = Spreadsheets.Open(Path & "\Examples\Datasets\Cat Clinic.sta")
    S1.Visible = True
    Dim obj2 As New StaCrossTab
    Dim S2 As Spreadsheet
    'Stack the 3 "weight" variables into one column, so that the old variable names are now
    'codes, and their respective values are in the "Value" column next to the codes.
    Set S2 = obj2.Flatten(S1, "3 6 9", "Value", "Code", False, False, "", "", "", "", True, 1, True, False, False)
    S2.Visible = True
End Sub