Max values by group ID

[fasha098]

Hi all, I need some help to obtain the max value and previous max value for each unique ID. Below is my dataset. I used the calculated values and Rank but it does not seem to work by grouping by ID. 

ID	Value
A	3
A	4
A	5
B	10
B	3
C	10

[andreas_lds]

this should work:

proc summary data=have nway;
  class ID;
  var Value;
  ouput out=want max=;
run;

[Sam_SAS]

Hello,

Can you tell us a little more about what you want to do? Do you just want to display the max value, or do you need to use the value in a calculated expression, etc?

I'm not sure what "previous max value" means if you do not have a date column.

A little more detail can help us help you.

Thanks,

Sam

[fasha098]

Hello, I want to display the largest two values for each ID.
My dataset is as follows:

ID	Value
A	3
A	4
A	5
B	10
B	3
B	7
C	10

The result should look like:

ID	Largest two values
A	4
A	5
B	10
B	7
C	10

[Sam_SAS]

The easiest way to do this in SAS Visual Analytics is apply a ranking to the report object. (Select the Ranks pane to make a ranking.) You can make a "top 2" ranking.

[this won't actually work for what you want, see below.]

Finding the second-greatest value in a VA expression might be trickier, although I'm sure there must be a way.

Let us know if a ranking will work for you.

Thanks,
Sam

[MarkusWeick]

Hi @Sam_SAS,
how do I get more than two lines (for example lines for all IDs) with a "top2" ranking in a list table?
Best
Markus

[Sam_SAS]

Of course @MarkusWeick is correct -- the ranking is for the full query and not for each category crossing. Let me see if I can figure something else out.

[MarkusWeick]

I have also been trying and haven't found a solution yet. 🙂

[Sam_SAS]

The dev team doesn't know of a way to do this within VA's expression editor.

You could precalculate the top-two values by using the topK CAS action, or you could use PROC RANK or PROC SUMMARY. Here is a blog post  that might be helpful.

Let us know if you get something to work for you!

I created a feature request for this. It would be great if you could specify "by-group" as the context for a rank on a Ranks panel.

Sam