SAS VA Designer 7.4 - Creating interactive reports when data are wihtin multiple facts tables

[Jonathan_]

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_Case	Date	Sexe	Age
1	2019-01-01	Men	18
2	2019-01-02	Women	24
3	2019-01-03	Men	32

Calls for each case :

ID_Case	ID_Call	Date
1	1	2019-01-01
1	2	2019-01-02
2	3	2019-01-02
3	4	2019-01-03
3	5	2019-01-13

Intoxication product for each case :

ID_Case	ID_Product
1	1
1	2
2	1
3	4
3	2
3	6

Intervention for each call :

ID_Call	ID_Intervention
1	1
1	2
3	1
5	6

Analyze for each call :

ID_Call	ID_Analyse
2	1
2	2
4	1
4	5

[FredrikE · Accepted solution]

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_]

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]

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