topic Max values by group ID in SAS Visual Analytics

Max values by group ID

fasha098 — Mon, 18 Jul 2022 06:24:50 GMT

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

Re: Max values by group ID

andreas_lds — Mon, 18 Jul 2022 08:28:16 GMT

this should work:

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

Re: Max values by group ID

Sam_SAS — Mon, 18 Jul 2022 13:19:53 GMT

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

Re: Max values by group ID

fasha098 — Mon, 18 Jul 2022 13:29:53 GMT

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

Re: Max values by group ID

Sam_SAS — Mon, 18 Jul 2022 14:38:25 GMT

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

Re: Max values by group ID

MarkusWeick — Mon, 18 Jul 2022 14:06:27 GMT

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

Re: Max values by group ID

Sam_SAS — Mon, 18 Jul 2022 14:24:19 GMT

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.

Re: Max values by group ID

MarkusWeick — Mon, 18 Jul 2022 14:29:08 GMT

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

Re: Max values by group ID

Sam_SAS — Mon, 18 Jul 2022 15:28:33 GMT

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