BookmarkSubscribeRSS Feed
Reaensh
Calcite | Level 5

Hi,

 

I have a current problem which hopefully someone with better expertise may be able to assist with.

 

There are 2 difference datasets I have:

 

1. This is raw data by each insurance claim and I create a varaible from the date in that data to have a column for total claims by months in 2016 (cicra 500,000 rows of data)

2. A plan claim numbers which is just 12 lines with a number of claims planned to receive

 

I want to be able to show in one data visualisation that when I sum up all the claims from Table 1, how they compare to what the plan exepcted in Table 2.

 

So it will for example say Jan-16, Actual 5000, Plan 4,000 and I can then do a comparison for the business to say when we are over or below plan.

 

Any advices on how I go about doing this?

 

 

Many thanks in advance

6 REPLIES 6
varsha_sas
SAS Employee

Hi, in SAS Visual Analytics, it's only possible to assign one table / data source to one visualization or object. Therefore, I would recommend that you either join the two tables and then create "actual" and "plan" columns to be added to the same list table or crosstab, or, you can keep these separate and assign them to separate list tables or crosstabs. You could put the tables and crosstabs side by side.

 

Hope that helps!

 

Varsha

Reaensh
Calcite | Level 5

Hi Varsha,

 

Thanks for this.

 

I am trying this and unsure how to start.

 

What is the best way to join the 2 tables then (through an interaction)?

 

Then how do I apply a formula to say if actual is over plan then say 105%, 20%, etc.

 

 

Thanks

itchyeyeballs
Pyrite | Level 9

You will probably need to join the two tables outside of the report/exploration tools.

 

VA has a data preperation tool that can do simple joins and manipluations of source data to create datasets that can then be used in reports.

 

If the join is more complex you could use one of the other SAS tools like data integration studio to process or you could potentially carry out this step in the data source itself (we are using SQL Server to store and pre-process most of our data)

 

Alternativley VA does support star schemas so if your second table is has a straightforward one to many join you could possibly link to it like that.

Lorrie_SAS
SAS Employee

Here's a link to the documentation for working with joins in the data builder:

 

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p0ynejicr0vyecn16q7or...

 

Best regards,

 

Lorrie

LOVE_SAA
Obsidian | Level 7

I think this is the fundamental design requirement of any reporting or visulization tool. But surprised that it doesn't support this feature

SASKiwi
PROC Star

You can have multiple data sources / tables in a VA report. A visualisation or a single report object is necessarily based on one data source / table which can be the result of joining several tables either in VA or when you prepare data outside VA. Please note a data source could be a single table or a star schema of multiple tables.

 

I don't follow why you think you can't incorporate multiple tables.

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
  • 6 replies
  • 6717 views
  • 2 likes
  • 6 in conversation