Fluorite | Level 6

## Percent of Row or Column Aggregations

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.

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%
1 ACCEPTED SOLUTION

Accepted Solutions
Pyrite | Level 9

## Re: Percent of Row or Column Aggregations

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

2 REPLIES 2
Pyrite | Level 9

## Re: Percent of Row or Column Aggregations

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

Fluorite | Level 6

## Re: Percent of Row or Column Aggregations

Thank so much, Petri. This is great.

Will go and study AggregateTable in more detail.

Kind Regards,

Kyle

Discussion stats
• 2 replies
• 2036 views
• 2 likes
• 2 in conversation