YTD calculation on the fly or with a table creation - stored process or EG

deleted_user — Mon, 24 May 2010 23:54:19 GMT

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

Re: YTD calculation on the fly or with a table creation - stored process or EG

deleted_user — Tue, 25 May 2010 14:04:26 GMT

I guess in my previous question, I combined multiple tasks at one time and complicated the issue. I need to do YTD calculation for cost by group variables.

In this case, I would like to have accumulating cost by scenario $ resource $ sku $ for each month. Thanks

Re: YTD calculation on the fly or with a table creation - stored process or EG

deleted_user — Tue, 25 May 2010 16:14:55 GMT

you can use proc tabulate

data test2;
set test;
if index(period,'10') then ytd = 10;
run;

proc tabulate data = test2;
var cost;
class ytd scenario sku resource;
table sku * resource * scenario , ytd * cost ;
run;

GL.

topic YTD calculation on the fly or with a table creation - stored process or EG in SAS Procedures

YTD calculation on the fly or with a table creation - stored process or EG

Re: YTD calculation on the fly or with a table creation - stored process or EG

Re: YTD calculation on the fly or with a table creation - stored process or EG