Percent of Row or Column Aggregations

[Kyle4]

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%

[PetriRoine · Accepted solution]

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:

1. + New data item -> Calculated item
2. Switch the editor from Visual to Text mode
3. 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

[Kyle4]

Thank so much, Petri. This is great.

Will go and study AggregateTable in more detail.

Kind Regards,

Kyle