I'm trying to screen a large data set for invalid categorical varaibles and want to screen by number of cases. Anyone know how I could get the number of cases in a variable? Like when you click a column on a spreadsheet, select "Variable Specs..." and then "Values\Stats" and you get a list of all the cases. I've figured out how to get some variable stats in SVB with this methodology but can't figure out where the functionality I want is in the object model.
retval = S1.Variable(varnum).Cases.Count
Also was wondering if you can add to VarBundle after its been created through SVB. I made a Bundle like this but can't seem to add to it after its been created.
varlist = "6 7"
Here is an example macro that might help you. You can modify bundles after creation. The example macro below has has a function FindBundleIndexByName which returns the bundle index for the given name, if it exists, or -1. Once you have the index, you can call s1.VarBundle.ItemVars(bundleIdx) = [a new bundle variable string].
Dim s1 As Spreadsheet
Set s1 = ActiveSpreadsheet
Dim varNumber As Long
varNumber = 1
Dim validN As Variant
' now validN is the number of non-missing cases
Dim values() As Double
s1.GetUniqueValues(varNumber, True, values)
' now values array has all the unique values from variable varNumber
Dim bundles As VarBundles
Dim bundleIdx As Long
bundleIdx = FindBundleIndexByName(s1, "MyBundle")
If bundleIdx <> -1 Then
' we found the bundle; modify it.
s1.VarBundle.ItemVars(bundleIdx) = "1 2"
' none found; add a new one
s1.VarBundle.Add("MyBundle", "6 7")
Function FindBundleIndexByName(s As Spreadsheet, bundleName As String) As Long
' find a bundle index given the name
' return -1 if not found
FindBundleIndexByName = -1
For i = 1 To s.VarBundle.Count
If s.VarBundle.ItemName(i) = bundleName Then
FindBundleIndexByName = i
You might also take a look at the "Filter Sparse Data" option, accessible from the Data menu, under Data Filtering/Recoding. This allows you to create a new spreadsheet including only those variables (or cases) that contain a specified percentage of valid (non-missing) values.
That's great thanks for sharing
Thanks for sharing .