SAS VA Variance calculations.

[vula]

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

[Sam_SAS]

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

[chrisjab]

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