YTD calculation should be based on the selected value in the time filter. As an example, if we put 'Feb-2010', then
YTD unit_cost for each specific 'scenario-period-sku-resource combination' calculated as total cost (4+7)/total output(10+13)., based on below data. Likewise for other categories and the YTD section looks like the output below with the given dataset = test.
data test;
input scenario $ period $ sku $ resource $ cost output;
cards;
Actual Jan-10 sku1 Material 4 10
Actual Jan-10 sku1 labor 5 11
Actual Jan-10 sku1 overhead 6 12
Actual Feb-10 sku1 Material 7 13
Actual Feb-10 sku1 Labor 8 14
Actual Feb-10 sku1 overhead 9 15
Actual Mar-10 sku1 Material 1 5
Actual Mar-10 sku1 labor 2 6
Actual Mar-10 sku1 overhead 3 7
Actual Jan-10 sku2 Material 5 5
Actual Jan-10 sku2 labor 6 6
Actual Jan-10 sku2 overhead 7 7
Budget 2010 sku1 Material 4 10
Budget 2010 sku1 labor 5 10
Budget 2010 sku1 overhead 6 12
;
run;
Required Final output:
Mar-10 Actual Budget YTD Actual YTD Budget
Material---Labor---Overhead---Total---Material---Labor---Overhead---Total---Material---Labor---Overhead---Total---Material---Labor---Overhead---Total
sku1--- 0.4--- 0.5--- 0.6--- 1.5--- 0.4--- 0.5--- 0.6--- 1.5--- 0.48--- 0.52--- 0.56--- 1.55--- 0.4--- 0.5--- 0.6---1.5---
I would prefer time filter as a macro variable and YTD calculation happens after selecting the time period but temporary table creation with YTD field on it, will also work. From the created table, I can pull the records based on the 'time filter'.
The developed code so far looks like without YTD calculation.
data test;
set test;
unit_cost=cost/output;
format unit_cost 8.1;
run;
proc report data=test nowd headline headskip;
where period in ('Jan-10','2010'); /*Date filter*/
column sku scenario,(resource, cost1 total);
define sku / group;
define scenario / across;
define resource / across order=data;
define unit_cost / sum;
define total / computed;
compute total;
_c5_=sum(_c2_,_c3_,_c4_);
_c9_=sum(_c6_,_c7_,_c8_);
endcomp;
run;
Thanks in advance for your help