topic Tabulate: Showing missing values when using formula derived categorical variables in Discussions

Tabulate: Showing missing values when using formula derived categorical variables

KD — Wed, 29 Aug 2018 20:35:03 GMT

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.

Re: Tabulate: Showing missing values when using formula derived categorical variables

txnelson — Wed, 29 Aug 2018 21:41:17 GMT

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.

Re: Tabulate: Showing missing values when using formula derived categorical variables

KarenC — Wed, 29 Aug 2018 22:00:31 GMT

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....

Re: Tabulate: Showing missing values when using formula derived categorical variables

KD — Thu, 30 Aug 2018 16:05:01 GMT

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.

Re: Tabulate: Showing missing values when using formula derived categorical variables

pmroz — Thu, 30 Aug 2018 16:08:38 GMT

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.

Re: Tabulate: Showing missing values when using formula derived categorical variables

KD — Thu, 30 Aug 2018 16:28:32 GMT

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!