Hi SAS Visual Analytics expert,
i'm quite new on SAS VA and i'm facing some problem using it.
I have to do something that could be quite simple to do with SAS BASE: for example i can't create a sequence variable base don some BY variable, and i can't use the lag function and i don't know how can i simulate them.
The problem here is that the table must change based on the filters the user can select.
For example, if i change the time period, the Sequence variable has to change its value based on the period selected.
I have this table:
Interaction | ID | Data |
1 | 1 | 01/01/2018 |
2 | 1 | 02/01/2018 |
3 | 1 | 03/01/2018 |
4 | 1 | 04/01/2018 |
5 | 2 | 03/01/2018 |
6 | 2 | 05/01/2018 |
7 | 2 | 07/01/2018 |
8 | 3 | 05/01/2018 |
9 | 3 | 07/01/2018 |
10 | 3 | 09/01/2018 |
And i want to create a sequence variable: i have to enumerate the records based on the ID column.
Interaction | ID | Data | Sequence |
1 | 1 | 01/01/2018 | 1 |
2 | 1 | 02/01/2018 | 2 |
3 | 1 | 03/01/2018 | 3 |
4 | 1 | 04/01/2018 | 4 |
5 | 2 | 03/01/2018 | 1 |
6 | 2 | 05/01/2018 | 2 |
7 | 2 | 07/01/2018 | 3 |
8 | 3 | 05/01/2018 | 1 |
9 | 3 | 07/01/2018 | 2 |
10 | 3 | 09/01/2018 | 3 |
Now i have to create some report:
1 Report | ||
N of contact | N multiple contact | avg day of recontact |
2 | 4.227 | 19 |
3 | 1.421 | 16 |
4 | 527 | 13 |
5 | 220 | 11 |
6 | 102 | 11 |
07.12 | 119 | 8 |
13-18 | 17 | 5 |
Tot | 6.633 | 17 |
Where:
N of contact: indicates the number of contacts(interactions) made by users. Essentially it counts the rows
N multiple contact: indicates the number of users who made multiple contacts, in relation to the N Contacts column. [527 users have contacted the company four times]
avg day of recontact: indicates the average time between one contact and the next
2 Report | |||
Max Contact | N of ID | % | N of contact |
1 | 12.189 | 74,30% | 12.189 |
2 | 2.806 | 17,10% | 5.612 |
3 | 894 | 5,40% | 2.682 |
4 | 307 | 1,90% | 1.228 |
5 | 118 | 0,70% | 590 |
6 to 10 | 93 | 0,50% | 625 |
>=11 | 9 | 0,10% | 123 |
Totale | 16.416 | 100% | 23.049 |
Max Contact indicates the maximum number of contacts made by users
N of ID: indicates the number of users who have made a n max contact. Ex: 894 users have contacted the company at most 3 times.
N of contact: indicates the number of contacts made by IDs who have contacted the Institute at most a n (Max Contact) number of times. Ex: The 894 IDs that contacted 3 times maximum, generated 2,682 interactions (894 * 3).
Maybe i should use a stored process in VA but i have never used it.
If it's a solution it is sufficient write the code in SAS Guide and then register it into sas va report?
What i was thinking, if there are no ways on doing what i want in SAS VA, is that the user can define the from-date and the to-date from VA. Then the procedure create the table and load them in memory so the report can be showed.
Thank you
Hi @FalkoSchulz, @TejaSurapaneni, @PeterWijers, @MichelleHomes
could you help me find a way to do this report.
I can also show other reports but the have to show the information a wrote above.
As mentioned in https://communities.sas.com/t5/SAS-Visual-Analytics/Visual-Analytics-First-var-Last-var/m-p/484219/h... - VA doesn't allow row based calculations such as sequence or lag (as these require special sorting to work). Did you try using path analysis to see whether this is suitable for your needs? Things like customer count on given paths and measuring overall customer journey / contact points is part of sankey diagrams.
Otherwise you could create a SAS job to create and update the table using data step. Yes, you can use SAS Enterprise Guide (or even SAS Studio if you are on SAS Viya already). Probably easier just to have the job replacing the in-memory table on regular basis so any report consumer gets updated values when viewing the report.
Hope this helps. Falko
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.