Count Distinct as measure

[Kamohelo]

Hi.

I would like to create a distinct count from a category data item as a measure. I would want to use this in a calculation. Is this possible to achieve within VA?

[acordes]

I think after creating the count distinct variable you'll need to "fix" it by creating an aggregated table with this variable. 

The aggregated table allows you to make calculations with it, in the original table it will result most likely in an error saying "you cannot do this with a aggregated measure"

this trick iften helps.

[Kamohelo]

Hi acordes

I have created a new distinct count from my ID category. and it has placed it under aggregate measure. I now created a new calculated item using aggregate table. I am getting errors. I am not sure if i am doing it the correct way. 

Your assistance is greatly appreciated

[acordes]

That's what I meant when saying that it will throw out an error.  

You have to make an aggregated table carrying over this newly created distinct count variable (plus the other variables you need). 

There you can use it as a static variable in your calculations. 

[Kamohelo]

Hi Acordes

Can you provide me with an example on how to perform this?

[acordes · Accepted solution]

I cannot access sas viya in these moments. 

But the steps are:

1. create distinct count of your categorical variable
2. create an aggregation of your table (go to data, choose your table, right click on create aggregation of this table)
   1. select your variables of interest plus the distinct count variable
3. once created the new aggregated table create calculated measure using the distinct count variable which had been transfered from the original table. now it behaves like a measure

[jtelesco70]

In older versions of SAS you didn't have to create a whole different data table just to create a simple distinct count.  There should be an easier way to do this.