This can be easily done in SQL outside of VA as two separate tables, then loaded into VA.
What type of reports do you want to do? Some reports will do counts without the need to prepare your data.
@gabras - If you want a solution that has a time dimension you need to add that to the data you posted so I know what your complete requirements are. As long as you summarise to the lowest level of granularity in your time dimension then that will work fine in VA. I have done this myself.
Hi @SASKiwi
let's say i have
ID | CUSTID | DATE |
1 | 1 | 01/01/2018 |
2 | 1 | 01/02/2018 |
3 | 1 | 02/02/2018 |
4 | 2 | 02/01/2018 |
5 | 2 | 03/01/2018 |
6 | 3 | 04/02/2018 |
7 | 3 | 05/02/2018 |
and i want to count how may interaction per custid
CUSTID | Count |
1 | 3 |
2 | 2 |
3 | 2 |
and then how may distinct CF have that amount of count
Count | CountDistCustId |
2 | 2 |
3 | 1 |
Now, i want to filter the data from february 2018 and so the different tables should become:
ID | CUSTID | DATE |
1 | 1 | 01/02/2018 |
2 | 1 | 02/02/2018 |
3 | 3 | 04/02/2018 |
4 | 3 | 05/02/2018 |
CUSTID | Count |
1 | 2 |
3 | 2 |
Count | CountDistCustId |
2 | 2 |
i hope it is clear
Thank you
If I understand your customer count correctly, something like this should work. When you then apply a filter on date VA will only include the customer counts that obey the filter.
proc sql;
create table want as
select date
,custid
,count(*) as Count
from have
group by date
,custid
;
quit;
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.