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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.