Tabulate: Showing missing values when using formula derived categorical variables

[#1]

Hello,

I am putting together a script to build a table that will use a formula derived categorical variable as the columns.  This scipt will be used on different data sets so I'd like to generalize it to show all values in the categorical variable even if the current data set does not show the value.

For example, say I have data set containing list of test subjects of different ages like so:

ID    Age

1    40

2    11

3    21

4    36

and I include a formula column called "Age Bin" that has 3 groups: "< 10", "10-20" and ">=20", the table becomes:

ID    Age    Age Bin

1    40        ">=20"

2    11        "10-20"

3    21        ">=20"

4    36        ">=20"

If I use Tabulate to get a table of number of test subjects in the different age bins, I'd get the following:

">=20"     "10-20"

     3               1

Since I'd like to run this script on different data sets, is there a way to force Tabulate to show "<10" with a 0 count even though there is none in the current data set?  The option "Include missing for grouping columns" does not apply for this case.  I'd like to do this in JSL but I don't even know to do this with the control panel so any help would be appreciated.

Thanks in advance.

[#2]

The only way that I know how to do this is to join/update a separate table that contains one entry for each group value.

[#3]

This is quick:

1. Use value lables column property and include all levels

2. Use categoical platfrom (Analyze>Consumer Research>Categorical

3. Use the simple, use the "include responeses not in data option"

4. Then save out the transposed frequencies...

Hopefully this can drive you in the right direction....

[#4]

Thanks for the help but this only works if I were analyzing categorical responses but I need to generalize the method to add general summary statistics for the other parameters in my analysis, which is why I wanted to use Tabulate in the first place.

I don't have a lot of columns in my data set so I can work around it for now and not have to build a bunch of separate tables. I was hoping there'd be a simpler way, thanks for the responses.

[#5 · Accepted solution]

To add to Jim's idea, add three rows for the standard age bins, and add a column indicating what the data row indicates:

ID	Age	Age Bin	Status
1	40	>=20	Data
2	11	10-20	Data
3	21	>=20	Data
4	36	>=20	Data
X	1	<10	Standard
Y	15	10-20	Standard
Z	25	>=20	Standard

When you tabulate these results, drag the Age Bin column onto the drop zone for rows, and the Status column onto the drop zone for columns.  This is the result:

You have an extra column now, but if you convert it to a table you can delete it.

[#6]

Great idea, that would work perfectly! Since my goal is to script it, I can easily add and remove those dummy columns so it won't even show up once the script is complete.

Thanks everyone!