Contributor
Posts: 20

# SAS VA Variance calculations.

Hi Techies,

Can any one provide solutions on variance calculations in SAS VA. Below is my data items and i need to show the basic table in cross tab.

Country _ Character
Client - Character
INTL - Numeric
Product - Character
Total - Numeric
Segment - Character

YRMONTH - EX - Date
 2015-01 2015-02 2015-03

In report variables = Quarter is custom category variable and DOM is calculated item i.e total-INTL.

Now i want to display the report in same table as shown in fig below.

Variance calculations are - Var \$ = Q1'16(INTL)-Q1'15(INTL) Var % = Var \$/Q1'15(INTL).

 Q1'16 Q1'15 Q4'15 Q1'16 v Q1'15 Q1'16 v Q4'15 Dollar\$m INTL DOM Total Int'l Mix INTL DOM Total Int'l Mix INTL DOM Total Int'l Mix Var \$ Var % Var \$ Var % 688 2,545 3,234 21% 693 2,593 3,286 21% 683 2,663 3,347 20% -             5 -1% 5 1% country 323 514 837 39% 318 507 825 39% 305 486 791 39% 5 1% 18 6% country 31 43 74 42% 33 45 78 42% 32 43 75 42% (2) -5% (1) -2% country 30 40 70 43% 32 43 75 43% 31 42 74 43% (2) -7% (2) -5% country 30 48 78 39% 33 52 85 39% 32 52 84 39% (3) -8% (2) -7% country 5 39 44 10% 5 40 44 10% 5 39 43 10% (0) -1% 0 1% country 28 56 84 33% 24 48 72 33% 28 56 83 33% 4 18% 0 1% country 89 1,004 1,093 8% 92 1,030 1,121 8% 95 1,067 1,162 8% (2) -3% (6) -6% country 33 - 33 100% 37 - 37 100% 35 - 35 100% (4) -10% (2) -6% country 10 153 163 6% 12 181 193 6% 17 246 263 6% (2) -16% (6) -38% country 15 105 120 12% 15 106 120 12% 14 102 116 12% (0) -1% 0 3% country 70 198 268 26% 69 195 265 26% 65 182 247 26% 1 1% 6 9% country 20 84 104 19% 20 85 105 19% 20 87 107 19% (0) -1% (1) -3% country 4 262 266 1% 4 262 266 1% 4 262 266 1% 0 0% 0 0% 688 2,545 3,234 21% 693 2,593 3,286 21% 683 2,663 3,347 20% (5) -1% 5 1%

Also How to calculate YOY

 Q1'15 Q1'16 Q4'15 YoY % 738 736 740 100%

Any guidence from techies would really help me a lot

Thanks

SAS Super FREQ
Posts: 322

## Re: SAS VA Variance calculations.

Hello,

I'm not sure I understand what you want to do, but I will try to help.

You are going to want to create aggregated measures rather than calculated items.

I think for your "VAR \$" you want to calculate the difference for each quarter's value versus the quarter from the previous year. You can use the ParallelPeriod operator to retrieve the value of the same quarter for the previous year.

So, here I am using Profit as the measure and Date is my date variable.

Sum [_ByGroup_] ('Profit'n) - ParallelPeriod(_Sum_, 'Profit'n, 'Date'n, _ByQuarter_, _ByYear_, -1, _Full_, {Date})

This compares each quarter's profit to the profit from the same quarter last year.

I'm not sure whether your Var % is supposed to compare the Var \$ value for the current quarter or for the previous year's quarter.

For the current quarter you can just do,

Var \$ /  Sum [_ByGroup_] ('INTL'n)

For the previous year's quarter,

'Var \$'n / ParallelPeriod(_Sum_, 'INTL'n, 'YRMONTH'n, _ByQuarter_, _ByYear_, -1, _Full_, {Date})

Is YOY different from your Var %? You should be able to create it using one of the periodic operators documented here:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n1lxnqfip132can1hdzue...

Hopefully this will get you on the right track, even if the expressions are not perfect for what you want.

Sam

Contributor
Posts: 23

## Re: SAS VA Variance calculations.

This approach works fine for a year over year. If I want to get a week over week view, what would be the best approach. Thanks

Discussion stats
• 2 replies
• 520 views
• 0 likes
• 3 in conversation