BookmarkSubscribeRSS Feed
Calcite | Level 5

I want to find out how many distinct individuals have clocked in per day in VA.


Imagine a person clocks in every time they enter a building. I don't care if they enter building multiply times a day, but want to know how many unique individuals clock in per day.

Data I have is f.i. like this:    

Monday John
Monday John
Monday Michael
Monday Jake
Monday Bob
Friday Charles
Friday John
Friday Charles
Friday Bob
Friday Jake
Friday John


If I use just a 'Count' on the "Name" then I get 23, which is not what I'm after, since this counts all duplicates during a day.

If I use 'Distinct' then I get 6, which is not what I'm after either, since it won't separate days.


I want a total Distinct count per day, so the number I'm after from this example is 18. 

So I want a dashboard to saying that number 18, fairly simple to achieve in Excel but I can't figure out how to do this in VA.


Basically I need a 'Distinct' count for all values in "Monday" then a 'Distinct' count for all values in "Tuesday" and so on. Then I need to summarize the days to get to the total for the week.

Is this possible to achieve directly in VA?   Or would I need to prep data and create a separate column for each day?

SAS Employee

In Visual Analytics 8.3+, you can use the AggregateTable operator and average the unique values of (name, day), then sum them up.


AggregateTable(_Sum_, Table(_Avg_, Fixed('name'n, 'day'n),'Frequency'n))



You can add this to a crosstab and enable the Totals option. All of the totals should add up for you.



@Renato_sas  wrote up a really great overview of the AggregateTable operator in case you'd like to learn more about it. It can do a lot of really neat things. You can check that out here: SAS Visual Analytics Advanced Calculations part 2 of 4


Calcite | Level 5

Family id       Member ID              Name

      1                500                   Akash Sahoo

      1                501                  Bikash Sahoo

      1                502                  Soumya Sahoo

      1                503                  Ashutosh Sahoo

      2                600                  Santosh Behera

      2                601                  Prasant  Behera

      2                602                  Sidharth Behera

      3                700                  Dibya Sharma

      3                701                  Rohit Sharma


Create  a new calculation variable in SAS Visual Analytics Total Family Member I want to show in given below


Family id       Member ID              Name                Total Family Members

      1                500                   Akash Sahoo                    4

      1                501                  Bikash Sahoo                    4

      1                502                  Soumya Sahoo                  4

      1                503                  Ashutosh Sahoo                4

      2                600                  Santosh Behera                3

      2                601                  Prasant  Behera                3

      2                602                  Sidharth Behera                3

      3                700                  Dibya Sharma                   2

      3                701                  Rohit Sharma                    2

How to calculate in SAS Visual Analytics above problem ?



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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
  • 2 replies
  • 3 in conversation