BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kyle4
Fluorite | Level 6

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  
  JanFeb Mar
ProductWidget1549
 Widget2714
 Widget311138
  231821
     
Want:    
  Month  
  JanFeb Mar
ProductWidget121.7%22.2%42.9%
 Widget230.4%5.6%19.0%
 Widget347.8%72.2%38.1%
  100.0%100.0%100.0%
1 ACCEPTED SOLUTION

Accepted Solutions
PetriRoine
Pyrite | Level 9

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.

Capture6.PNG

 

I hope this helps 😃

 

Best regards,
Petri

View solution in original post

2 REPLIES 2
PetriRoine
Pyrite | Level 9

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.

Capture6.PNG

 

I hope this helps 😃

 

Best regards,
Petri

Kyle4
Fluorite | Level 6

Thank so much, Petri. This is great.

 

Will go and study AggregateTable in more detail.

 

Kind Regards,

Kyle

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

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