Create Bar chart from table 1 and Key value from table 2
Bar chart – Description wise unique records
Key value – Total amount
Try to see if we can create interactions between these two tables.
There are two different tables loaded in the cas server, and the client did not want to join the table. , and they want to create two different charts and want to give the intersection between those two charts.
Intersection or interaction? These are not the same.
"Intersection between charts" is not a meaningful concept in my mind.
If we can't merge the tables (why not?), then how can we proceed?
The whole thing doesn't make sense to me, and instead of these very brief instructions and very brief explanation, perhaps you could give a more detailed set of instructions and a more detailed explanation.
@PaigeMiller - Below is the detailed view on the query:
One of our client is working on SAS VA (on Viya) and they have multiple tables which they are using for their report development and multiple reports are prepared from respective tables.
For few reports, they wanted to have interactions between two charts which are created from 2 different tables. The challenge is that all the tables are there in CAS (in-memory) and the client don't want to go back to data preparation to join these tables for these specific reports, as they have limited memory in RAM and few of these table heavy (in terms of size).
Now, below is an illustrative example which I have shared for a sample data:
Table1 has a unique id with description:
Table2 has a unique id with amount:
If we create a simple bar chart based on Table 1 with description and unique id and second chart as key value with total amount based on table 2.
Now, we want create an interaction between bar chart and key value. Currently, with SAS VA (on Viya) we are not able to make interaction as both table doesn't have the common value present in the bar chart.
Can it be possible to create such kind of interactions in SAS VA (on Viya) or SAS VA 7.5 where we do not need to join multiple data table but the chart interactions can happen on the fly using the common unique id present in both tables but common unique id is not shown in any chart or table.
Sorting and joining depends purely on disk space, so RAM is not a showstopper.
If the performance of SQL is a problem, doing two sorts and joining in a data step is just a little more typing, but that's all.
proc sql also runs in CAS, so you should be able to use my SQL in Viya.
@Kurt_Bremser - I understand the Proc SQL and Dataset is the prime solution to this problem, which we also proposed to client.
But client do not want to write any data step or proc sql query for these reports. As some of these report would self-service reports where business users will login in SAS VA and develop reports on the fly and most of these business users would be non-technical resources who can work only using simple drag and drop features.
In such situation, we cannot expect them to write code to create another dataset for the same and then use the same in respective report. What we are looking for is a possible alternative solution where we donot have to write any SQL or data step statement and we can directly create interactions in SAS VA.
Do you mean this join?
proc sql; create table want as select a.description, sum(b.amt) as amt from table1 a inner join table2 b on a.unique_id = b.unique_id group by a.description; quit;
and then point-and-click a bar chart from want.
This respons is valid in VA 7.3 (You have not stated what version of VA you are using - would be helpfull to know)
1) Make sure your Unique_id is a category
2) Make sure that it's also been used in both visualizations
Btw: You could remove Unique from the List Table object (not needed, just added it for clarity)
Next you add the interaction (remember it will only work on visible columns in Object 1 - here the Bar Chart)
Select Unique_id from both tables.
SAS is headed back 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.
Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!
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.