BookmarkSubscribeRSS Feed
fasha098
Calcite | Level 5

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. 

IDValue
A3
A4
A5
B10
B3
C10
8 REPLIES 8
andreas_lds
Jade | Level 19

this should work:

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

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
Calcite | Level 5

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

IDValue
A3
A4
A5
B10
B3
B7
C10

 

The result should look like:

IDLargest two values
A4
A5
B10
B7
C10
Sam_SAS
SAS Employee

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
Barite | Level 11
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
Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Sam_SAS
SAS Employee

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
Barite | Level 11

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

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Sam_SAS
SAS Employee

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 8 replies
  • 1415 views
  • 2 likes
  • 4 in conversation