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 |
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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.