Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How to incorporate 2 tables in one data visualisation

Reply
Occasional Contributor
Posts: 9

How to incorporate 2 tables in one data visualisation

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

SAS Employee
Posts: 29

Re: How to incorporate 2 tables in one data visualisation

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

Occasional Contributor
Posts: 9

Re: How to incorporate 2 tables in one data visualisation

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

Frequent Contributor
Posts: 98

Re: How to incorporate 2 tables in one data visualisation

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.

SAS Employee
Posts: 35

Re: How to incorporate 2 tables in one data visualisation

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

Ask a Question
Discussion stats
  • 4 replies
  • 337 views
  • 0 likes
  • 4 in conversation