BookmarkSubscribeRSS Feed
znhnm
Quartz | Level 8

Hi,

 

I have a start and end date in my data set.  I calculated a new item called "number of days" as the number of days in between these two dates. I also calculated the average number of days using the median which is 59. Now, I would like to count how many of my records are above the average number of days and how many are below the average. What would be the calculated item formula for it?  For example, assuming I have these 5 records on my dataset:

Number of Days 
110
100
45
55

25

 

Then, my new calculated item "above average" would have the value 2. and My new calculated item "below average" would have the value 3. So, I could create a key value objects using these values as the measure. 

1 REPLY 1
Sam_SAS
SAS Super FREQ

Hello,

 

This is a tricky one. I think you will need to precalculate the median value as part of your data preparation.

 

VA expression syntax does not like to compare numerics to aggregated measures, so you cannot simply calculate the median (which returns an aggregated measure) and compare that to the number of days.

 

So if you precalculate the median (using PROC MEANS, for example) as a regular measure column, then you can create an expression based on that.

 

You could make your two calculations as follows:

 

Above Average:

IF ('Number of days'n >= 'Median'n) RETURN 1 ELSE 0

 

Below Average:

IF ('Number of days'n < 'Median'n) RETURN 1 ELSE 0

 

You can now apply Sum aggregation to these to get the count.

 

As always, there might be a better, simpler solution I'm not thinking of.

 

Let us know if that helps,

Sam

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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
  • 1 reply
  • 269 views
  • 0 likes
  • 2 in conversation