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 |
this should work:
proc summary data=have nway;
class ID;
var Value;
ouput out=want max=;
run;
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
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 |
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
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.
I have also been trying and haven't found a solution yet. 🙂
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.