BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jonathan_
Calcite | Level 5

Hi,

 

In the SAS Visual analytics designer 7.4, I would like to create an interactive reports with interactions and filters but the data are wihtin multiple facts tables.

 

So, I would like to know what is the best way to create a VA report using these type of data, do i need to join all the tables ?

How can i create rate over year if the population per year is in an other database and incidence cases are in another database ?

 

For example, I have cases (50 000 cases per year) that have calls associated with and for each case or calls I can have interventions, product intoxication, analyzes made by a doctor and so on. So, if i join all the tables I will have a table with high volume  over years (millions of lines). Here a small example of the data.

 

For an example of reports, I would need to set promps for date, sexe, age, products and present these kind of information :

 

Trend over-time for cases (number and rates)

Trend over-time for calls

Cross table of different variables for cases

Cross table of different variables for calls

Top 10 Intoxication product

Top 10 Analyzes

Top 10 Interventions

 

Regards,

 

Cases :

 

ID_CaseDateSexeAge
12019-01-01Men18
22019-01-02Women24
32019-01-03Men32

 

Calls for each case :

 

ID_CaseID_CallDate
112019-01-01
122019-01-02
232019-01-02
342019-01-03
352019-01-13

 

Intoxication product for each case :

 

ID_CaseID_Product
11
12
21
34
32
36

 

Intervention for each call :

 

ID_CallID_Intervention
11
12
31
56

 

Analyze for each call :

 

ID_CallID_Analyse
21
22
41
45

 

1 ACCEPTED SOLUTION

Accepted Solutions
FredrikE
Rhodochrosite | Level 12

Hi!

I would say that if you want to do calculations using variables from different tables you need to join them.

This can be done using built in data prep or before loading data to VA, enterprise guide is my favorite.

 

If you on the other hand want to subset data using different tables you can link them together in the designer. You need to have the corresponding variable in the tables you're are linking 🙂

 

//fredrik

View solution in original post

3 REPLIES 3
FredrikE
Rhodochrosite | Level 12

Hi!

I would say that if you want to do calculations using variables from different tables you need to join them.

This can be done using built in data prep or before loading data to VA, enterprise guide is my favorite.

 

If you on the other hand want to subset data using different tables you can link them together in the designer. You need to have the corresponding variable in the tables you're are linking 🙂

 

//fredrik

Jonathan_
Calcite | Level 5

Hi FredrikE,

 

Thanks for your answer, my first guess was to join all the tables using SAS Enterprise Guide as you suggest, but It would have been nice if instead of joining all of them I would have been able to link those tables based on the different ID variables (ID_case, ID_calls, ...) and then set prompts from variables that appeared in different tables. For example, make a prompt for sexe (this variable is present in the case table) and then filter the calls table using the ID_calls.

 

Regards,

 

Jonathan

FredrikE
Rhodochrosite | Level 12

As always (!) you have more functionality in the newer versions of VA, especially in 8.3 you can do joins and aggregations (you can for example create an aggregated table and then join it with the original, perfect for adding totals to details for comparison...) much more dynamic directly in VA without the need to do it before loading into VA 🙂

 

//Fredrik

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 631 views
  • 0 likes
  • 2 in conversation