topic Re: How to count Unique/Distinct values in VA? From parts of a spreadsheet in SAS Visual Analytics

How to count Unique/Distinct values in VA? From parts of a spreadsheet

Crae — Thu, 18 Nov 2021 15:07:25 GMT

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
Tuesday	Bob
Tuesday	Jake
Tuesday	Michael
Tuesday	John
Wednesday	Bob
Wednesday	Bob
Wednesday	Jake
Thursday	Charles
Thursday	Jacob
Thursday	John
Thursday	Bob
Thursday	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?

Re: How to count Unique/Distinct values in VA? From parts of a spreadsheet

Stu_SAS — Thu, 18 Nov 2021 22:09:43 GMT

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

Re: How to count Unique/Distinct values in VA? From parts of a spreadsheet

Binayak97 — Mon, 15 May 2023 10:21:35 GMT

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 ?

Re: How to count Unique/Distinct values in VA? From parts of a spreadsheet

Renato_sas — Thu, 30 May 2024 17:05:18 GMT

I just came across this old question that for some reason was left unanswered. The solution is similar to what @Stu_SAS had posted before:

Total Family Members = AggregateTable(_Sum_, Table(_Sum_, Fixed("Family id"n), 1))