topic Re: SASVA count distinct of certain column in SAS Visual Analytics

SASVA count distinct of certain column

GreyHamster — Wed, 14 Oct 2020 14:32:17 GMT

I have the following data, I'd like to create a report with a cross table.

rec_id	company_id	signals	month
1000	1	1	October
1001	1	0	October
1002	2	1	October
1003	2	1	October
1004	2	0	October
1005	3	1	October

In the report I would like to have

the count of the rec_id

the distinct count of company_id (which I did with aggregated value and distinct)

the sum of signals

a signal per company_id so for company A 1 signal, B 1 signal (not 2 signals) and C 1 signal

The signal column doesn't have values more than 1.

How can I create such an aggregated value?

Re: SASVA count distinct of certain column

PetriRoine — Thu, 15 Oct 2020 08:26:06 GMT

Hello @GreyHamster ,

Which version of VA you have?
You mentioned that you'd like to create a report with a cross table. How should the resulting cross table look like? Could you sketch it?

Creating needed variables:

the count of the rec_id
Hover mouse over rec_id > click r-m-b > New calculation... > Type = Count
the distinct count of company_id (which I did with aggregated value and distinct)
Hover mouse over company_id > click r-m-b > New calculation... > Type = Distinct Count
the sum of signals
Check that measures aggregation is Sum
a signal per company_id so for company A 1 signal, B 1 signal (not 2 signals) and C 1 signal
I'm not sure if I understand this. Will the result be always 1? Is there any possibility that it will be zero or something else? If it is always one then let's just create a New Calculated Item with simple 1 and set aggregation to Min or Max. If it's something else we might need utilize AggregateTableI() operator. And finally how should this result be embedded to the Cross table

Best regards,

Petri

Re: SASVA count distinct of certain column

GreyHamster — Thu, 15 Oct 2020 12:57:19 GMT

Thanks for your answers, with AggregateTable it can probably do what I would want to do, unfortunately I'm on version 7.5.

I want to know whether there is a signal for a company.
If I would build a query I would create an aggregated table with company_id and the sum of signals and another query to check if the signal=>1 then 1 else 0 for each company.

Instead of creating a new datasource I would want to do it in an expression. If I can't use aggregatetable, is there another way?