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

- JMP User Community
- :
- Discussions
- :
- Merging Rows with Sum

- 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

Sep 16, 2020 10:34 AM
(146 views)

Help! I have a datatset with 200ish rows. There are 170ish unique IDs, meaning multiple people have multiple rows. Is there a way to merge the data from the applicable rows (e.g., some rows are dates so that would not be applicable) with a sum? Such that each unique subject ID only has ONE row in the dataset. If its only possible to this with a dataset with applicable rows (i.e., all numeric/continuous variables), I could subset my data to just subject ID + the needed rows for summing. Does this make sense?

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

OK....here is a display of a data table that has 200 rows, with 118 different IDs and contains a date and an Expense column.

What I want is a data table with just one entry per ID and for the date to be the last date the person had a transaction, and I want to know the sum of the expenses. So I go to

Tables=>Summary

and it brings up the following Dialog window which I fill in the information to group by IDs, return the Maximum Date value and the Sum of the Expenses

I then click on the OK button and it creates a data table with the summary statistics

I could have also gotten the same results by using the Tabulate Platform

Analyze=>Tabulate

It is a drag and drop report writer, that once you have the report the way you want, you can go to the red triangle and request that a data table be made from the report

Jim

5 REPLIES 5

Highlighted
##

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

Re: Merging Rows with Sum

I assume this is the same question you subsequently added to a previous discussion topic. The answer is to use the Summary platform

Tables=>Summary

Tables=>Summary

Jim

Highlighted
##

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

Re: Merging Rows with Sum

Thanks. Can you elaborate?

Highlighted

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

OK....here is a display of a data table that has 200 rows, with 118 different IDs and contains a date and an Expense column.

What I want is a data table with just one entry per ID and for the date to be the last date the person had a transaction, and I want to know the sum of the expenses. So I go to

Tables=>Summary

and it brings up the following Dialog window which I fill in the information to group by IDs, return the Maximum Date value and the Sum of the Expenses

I then click on the OK button and it creates a data table with the summary statistics

I could have also gotten the same results by using the Tabulate Platform

Analyze=>Tabulate

It is a drag and drop report writer, that once you have the report the way you want, you can go to the red triangle and request that a data table be made from the report

Jim

Highlighted
##

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

Re: Merging Rows with Sum

That was INCREDIBLY helpful. Thanks so much!

Highlighted
##

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

Re: Merging Rows with Sum

Now you need to go to

Help=>JMP Documentation Library

and read both the Discovering JMP and the Using JMP documents!!!

It will make your time with JMP far more enjoyable.

Help=>JMP Documentation Library

and read both the Discovering JMP and the Using JMP documents!!!

It will make your time with JMP far more enjoyable.

Jim