How to Group/ Count Records Based on an Aggregated Measure in SAS Visual Analytics

[saunvida]

Hi everyone,

I have alert-level data in SAS Visual Analytics. Each row corresponds to a specific alert (SCN) raised for a claim, and includes fields like CLAIM_ID, SCN_NAME (alert_id), RESPONSE (which can be "Triggered" or "Not Triggered").

My goal is to build a visualization that shows: How many claims had 1 triggered alert, how many claims had 2 triggered alerts so on.

So essentially I want to:

• Count the number of triggered alerts per claim

• Then group claims by that count (e.g., 50 claims had 2 triggered alerts, 30 claims had 3, etc.)

I created an aggregated measure to count triggered alerts per claim:

But I’m stuck because I cannot group by this aggregated measure directly to count how many claims fall into each group (like "3 triggered alerts").

Could someone guide me on how to do this in SAS VA 8.x? Do I need to use a custom category or prepare the data differently?

Thanks in advance!

[TSBruce]

I am not sure what the end result should look like, but I think you should be able to at least get what you need using an aggregated data source and a join.  Please keep in mind that this approach creates temporary tables in memory and can consume resources, so may not be appropriate if your data are large or if you have a large number of concurrent users.  Here are some steps I took to get the results and assumes you can change the input data slightly:

1.  Create a new variable in your source data that concatenates RESPONSE and CLAIM_ID (I called this RESPONSE_CLAIM)

2.  Once this has been added to your source table and reloaded, create a new measure (I called this REPONSE_COUNT) with logic similar to below:

IF ( Substring('Response_Claim'n, 1, 3) = 'Tri' )
RETURN 1
ELSE 0

3.  Create an aggregated data source using the concatenated data item and the calculated measure from above (RESPONSE_COUNT)

4.  Join your original table with the aggregated data source using a left join with RESPONSE_CLAIM being the key and selecting the columns you would like to use.

Again, I am not sure what your desired outcome is and this may need some tweaking, but the screenshot below shows a table showing the number of triggered alerts for claims.  This table filters the lower table that contains detail records so that clicking on the top table shows details for that claim.

Hope this helps!

[saunvida]

Hi,
Thank you for your earlier response. It was helpful as an intermediate step. I’d like to clarify my end goal a bit more clearly:

Ultimately, I’m looking to create either a list table or a histogram that shows how many claims have 1, 2, 3, 4, etc., alerts. Ideally, the final output would resemble something like this: 

So, the goal here is to see how many claims fall into each bucket of alert count. 

I’d really appreciate your guidance on how best to structure this in SAS VA. Thanks again for your support!

[TSBruce]

You should be able to get the results you want.  One quick correction on my last post - you will want to convert RESPONSE_COUNT in your aggregated data source to a category instead of leaving this as a measure.  once you have done this, you should be able to create a calculated data item in your joined data source that calculates the distinct count for CLAIM_ID.  You can then use RESPONSE_COUNT (which is a category) and the distinct count of CLAIM_ID from the joined data source in your dashboard.  Below is an updated picture based on your requested changes.