Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Tabulate: Showing missing values when using formula derived categorical variable...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 29, 2018 1:35 PM
(2513 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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.

Jim

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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!

Thanks everyone!