topic Re: Percent of Row or Column Aggregations in SAS Visual Analytics

Percent of Row or Column Aggregations

Kyle4 — Wed, 01 Apr 2020 09:56:59 GMT

Hi,

How do I calculate percentage of row or percentage of column calculation in a crosstab in SAS Viya VA 8.5? I would like to be able to do it based on frequency and another measure (for instance sales $ value). The total value (100%) is not required.

Thanks in advance.

Regards,

Kyle

Ex.

Have:
		Month
		Jan	Feb	Mar
Product	Widget1	5	4	9
	Widget2	7	1	4
	Widget3	11	13	8
		23	18	21

Want:
		Month
		Jan	Feb	Mar
Product	Widget1	21.7%	22.2%	42.9%
	Widget2	30.4%	5.6%	19.0%
	Widget3	47.8%	72.2%	38.1%
		100.0%	100.0%	100.0%

Re: Percent of Row or Column Aggregations

PetriRoine — Thu, 02 Apr 2020 13:00:15 GMT

Hello @Kyle4 ,

Please try the AggregateTable operator that was introduced in VA 8.3. Here is code snippet which you can easily copy-paste to your editor.

Steps:

+ New data item -> Calculated item
Switch the editor from Visual to Text mode
Copy paste the code below
I assumed your variable names were: Month, Product, and Measure.

AggregateTable(_Sum_, Table(_Sum_, Fixed('Product'n, 'Month'n),
'Measure'n)) / AggregateTable(_Sum_, Table(_Sum_, Fixed('Month'n)
, 'Measure'n))

And here is how my Crosstab looks like.

I hope this helps 😃

Best regards,
Petri

Re: Percent of Row or Column Aggregations

Kyle4 — Fri, 03 Apr 2020 12:36:05 GMT

Thank so much, Petri. This is great.

Will go and study AggregateTable in more detail.

Kind Regards,

Kyle