There are different scenarios related to generating reports based on grouping columns.

This example shows how to use a task instead of writing code in SAS^® Enterprise Guide to return the highest value of a secondary group column for each unique value of a primary group column.

Starting with some sample data:

  Group_1   Group_2

      1       100
      1       400
      1       200
      2       180
      2       150
      2       175
      3       160
      3       125
      4       300
      4       330
      4       333
      5       400
      5       900
      5       800

The goal in this example is to return one row for each Group_1 value, and for each Group_1 value to have the highest Group_2 value.

The resulting table should be:

  Group_1  Group_2

      1      400
      2      180
      3      160
      4      333
      5      900

Follow these steps in SAS Enterprise Guide to get the resulting data:

1. File => Open to add the input data to the project.
2. From the Data menu, choose Sort Data.
3. Assign Group_1 and Group_2 to the Sort by Task roles.
4. Notice to the far upper right, there is a ComboBox that allows you to select the Sort order.

   Select Group_2 under the Task roles and choose Descending for the Sort order.

5. Then Run the task.
6. With the Sorted data selected, choose the Data menu and choose Sort Data.
7. Assign Group_1 to the Sort by Task roles.
8. Choose the Options tab.
9. Under 'Duplicate records', select 'Keep only the first record for each 'Sort by' group.
10. Run the task.

The resulting table will have one row for each Group_1 value with the highest Group_2 value.

Operating System and Release Information