Please find the attached file for OPEX Dump and the complete program is below which i need to cut short:
libname op '/amex/ramp/access/bdev_msa/ngirdhar/Opex/DS/';
libname shared '/amex/ramp/access/bdev_msa/Shared';
PROC IMPORT DATAFILE= "/amex/ramp/access/bdev_msa/ngirdhar/Opex/Data/Opex_dump_JAN_2017.xlsx"
OUT= op.dump
DBMS=xlsx REPLACE ;
GETNAMES=YES;
RUN;
/*data op.dump(rename='Line Item'n=Line_Item);*/
/*set op.dump;*/
/*run;*/
data op.dump_1;
set op.dump;
ytd_ly_jan=sum(Jan2016);
ytd_ly_feb=sum(Jan2016,Feb2016);
ytd_ly_mar=sum(Jan2016,Feb2016,Mar2016);
ytd_ly_apr=sum(Jan2016,Feb2016,Mar2016,Apr2016);
ytd_ly_may=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016);
ytd_ly_jun=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016);
ytd_ly_jul=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016,Jul2016);
ytd_ly_aug=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016,Jul2016,Aug2016);
ytd_ly_sep=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016,Jul2016,Aug2016,Sep2016);
ytd_ly_oct=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016,Jul2016,Aug2016,Sep2016,Oct2016);
ytd_ly_nov=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016,Jul2016,Aug2016,Sep2016,Oct2016,Nov2016);
ytd_ly_dec=sum(Jan2016,Feb2016,Mar2016,Apr2016,May2016,Jun2016,Jul2016,Aug2016,Sep2016,Oct2016,Nov2016,Dec2016);
ytd_actual_jan=sum(Jan2017);
ytd_actual_feb=sum(Jan2017,Feb2017);
ytd_actual_mar=sum(Jan2017,Feb2017,Mar2017);
ytd_actual_apr=sum(Jan2017,Feb2017,Mar2017,Apr2017);
ytd_actual_may=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017);
ytd_actual_jun=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017);
ytd_actual_jul=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017,Jul2017);
ytd_actual_aug=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017,Jul2017,Aug2017);
ytd_actual_sep=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017,Jul2017,Aug2017,Sep2017);
ytd_actual_oct=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017,Jul2017,Aug2017,Sep2017,Oct2017);
ytd_actual_nov=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017,Jul2017,Aug2017,Sep2017,Oct2017,Nov2017);
ytd_actual_dec=sum(Jan2017,Feb2017,Mar2017,Apr2017,May2017,Jun2017,Jul2017,Aug2017,Sep2017,Oct2017,Nov2017,Dec2017);
ytd_plan_jan=sum('Jan forecast2017'n);
ytd_plan_feb=sum('Jan forecast2017'n,'Feb forecast2017'n);
ytd_plan_mar=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n);
ytd_plan_apr=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n);
ytd_plan_may=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n);
ytd_plan_jun=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n);
ytd_plan_jul=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n,'Jul forecast2017'n);
ytd_plan_aug=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n,'Jul forecast2017'n,'Aug forecast2017'n);
ytd_plan_sep=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n,'Jul forecast2017'n,'Aug forecast2017'n,'Sep forecast2017'n);
ytd_plan_oct=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n,'Jul forecast2017'n,'Aug forecast2017'n,'Sep forecast2017'n,'Oct forecast2017'n);
ytd_plan_nov=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n,'Jul forecast2017'n,'Aug forecast2017'n,'Sep forecast2017'n,'Oct forecast2017'n,'Nov forecast2017'n);
ytd_plan_dec=sum('Jan forecast2017'n,'Feb forecast2017'n,'Mar forecast2017'n,'Apr forecast2017'n,'May forecast2017'n,'Jun forecast2017'n,'Jul forecast2017'n,'Aug forecast2017'n,'Sep forecast2017'n,'Oct forecast2017'n,'Nov forecast2017'n,'Dec forecast2017'n);
run;
data op.dump_format;
set op.dump_1;
format Desc $100.;
code = scan(Line_Item,1," ");
if Line_Item = 'MS Cost Metrics Before Charges' then Desc = 'Cost Metrics Before Charges';
if Line_Item = 'M67002 Salaries and Employee Benefits' then Desc = 'Salaries and Employee Benefits';
if Line_Item = 'M66003 Regular Payroll' then Desc = 'Regular Payroll';
if Line_Item = '903001 Salary Expenses For Temporary Employees' then Desc = 'Temporary Payroll';
if Line_Item = '904001 Overtime Salary Expense To Nonexempt Employees' then Desc = 'Overtime';
if Line_Item = 'M66004 Incentive Bonus Provision' then Desc = 'Incentive Bonus Provision';
if Line_Item = 'M66005 Payroll Costs & Benefits' then Desc = 'Payroll Costs & Benefits';
if Line_Item = 'M66006 Outside Temporary Assistants' then Desc = 'Outside Temporary Assist';
if Line_Item = '924001 Expense For Temporary Information On Technology Contractors' then Desc = 'Outside DP Contractors';
if Line_Item = '923001 Employee Education Assistance' then Desc = 'Employee Education';
if Line_Item = 'M66007 Professional Services' then Desc = 'Professional Services';
if Line_Item = 'M66012 Legal, Audit & Prof. Fees' then Desc = 'Legal, Audit & Prof. Fees';
if Line_Item = '949010 External Sales Agent Commission' then Desc = 'External Sales Agent Commission';
if Line_Item = 'M66013 Credit & Collection' then Desc = 'Credit & Collection';
if Line_Item = 'M66008 Occupancy & Equipment' then Desc = 'Occupancy & Equipment';
if Line_Item = 'M66014 Data Processing' then Desc = 'Data Processing';
if Line_Item = 'M66015 Occupancy & Other Equipment' then Desc = 'Occupancy & Other Equipment';
if Line_Item = 'M63024 Real Estate' then Desc = 'Real Estate';
if Line_Item = 'M66009 Communications' then Desc = 'Communications';
if Line_Item = 'M66016 Postage & Distribution' then Desc = 'Postage & Distribution';
if Line_Item = 'M66017 Telephone & Communication' then Desc = 'Telephone & Communication';
if Line_Item = 'M63003 Operating Expenses before Service Fees' then Desc = 'Operating Expenses before Service Fees';
if Line_Item = 'M62021 Printing & Stationery' then Desc = 'Printing & Stationery';
if Line_Item = 'M62019 Travel & Entertainment' then Desc = 'Travel & Entertainment';
if Line_Item = 'M62022 Misc Operating Expenses' then Desc = 'Other Operating Expenses';
if Line_Item = 'M62020 Taxes Other than Income' then Desc = 'Taxes Other than Income';
if Line_Item = 'M63014 Non Utility Svc Fees' then Desc = 'Non Utility Svc Fees';
if Line_Item = 'Tech' then Desc = 'Tech';
if Line_Item = 'Other (Non-Tech/Direct Ops)' then Desc = 'Other (Non-Tech/Direct Ops)';
if Line_Item = 'Total Less Legal' then Desc = 'Total Less Legal';
run;
proc sql;
create table ly as
select distinct
RC,
GM,
SVP,
VP,
Line_Item as Code_line_item,
Desc as Line_item,
Hierarchy,
Jan2016, Feb2016, Mar2016, Apr2016, May2016, Jun2016, Jul2016, Aug2016, Sep2016, Oct2016, Nov2016, Dec2016
from op.dump_format;
create table ytd_ly as
select distinct
RC,
GM,
SVP,
VP,
Line_Item as Code_line_item,
Desc as Line_item,
Hierarchy,
ytd_ly_Jan,ytd_ly_Feb,ytd_ly_Mar,ytd_ly_Apr,ytd_ly_May,ytd_ly_Jun,ytd_ly_Jul,ytd_ly_Aug,ytd_ly_Sep,ytd_ly_Oct,ytd_ly_Nov,ytd_ly_Dec
from op.dump_format;
create table cy as
select distinct
RC,
GM,
SVP,
VP,
Line_Item as Code_line_item,
Desc as Line_item,
Hierarchy,
Jan2017, Feb2017, Mar2017, Apr2017, May2017, Jun2017, Jul2017, Aug2017, Sep2017, Oct2017, Nov2017, Dec2017
from op.dump_format;
create table ytd_cy as
select distinct
RC,
GM,
SVP,
VP,
Line_Item as Code_line_item,
Desc as Line_item,
Hierarchy,
ytd_actual_Jan,ytd_actual_Feb,ytd_actual_Mar,ytd_actual_Apr,ytd_actual_May,ytd_actual_Jun,ytd_actual_Jul,ytd_actual_Aug,ytd_actual_Sep,ytd_actual_Oct,ytd_actual_Nov,ytd_actual_Dec
from op.dump_format;
create table plan_cy as
select distinct
RC,
GM,
SVP,
VP,
Line_Item as Code_line_item,
Desc as Line_item,
Hierarchy,
'Jan Forecast2017'n as Jan_Forecast2017, 'Feb Forecast2017'n as Feb_Forecast2017, 'Mar Forecast2017'n as Mar_Forecast2017, 'Apr Forecast2017'n as Apr_Forecast2017,
'May Forecast2017'n as May_Forecast2017, 'Jun Forecast2017'n as Jun_Forecast2017, 'Jul Forecast2017'n as Jul_Forecast2017, 'Aug Forecast2017'n as Aug_Forecast2017,
'Sep Forecast2017'n as Sep_Forecast2017, 'Oct Forecast2017'n as Oct_Forecast2017, 'Nov Forecast2017'n as Nov_Forecast2017, 'Dec Forecast2017'n as Dec_Forecast2017
from op.dump_format;
create table ytd_plan_cy as
select distinct
RC,
GM,
SVP,
VP,
Line_Item as Code_line_item,
Desc as Line_item,
Hierarchy,
ytd_plan_Jan,ytd_plan_Feb,ytd_plan_Mar,ytd_plan_Apr,ytd_plan_May,ytd_plan_Jun,ytd_plan_Jul,ytd_plan_Aug,ytd_plan_Sep,ytd_plan_Oct,ytd_plan_Nov,ytd_plan_Dec
from op.dump_format;
quit;
proc transpose data=ly out=ly_trans (drop= _LABEL_ rename=ly1=LY )
name = month
prefix= ly;
By RC
GM
SVP
VP
Code_line_item
Line_item
Hierarchy;
run;
data ly_trans_1;
attrib month format=$CHAR16. ;
set ly_trans;
run;
proc transpose data=ytd_ly out=ytd_ly_trans (drop= _LABEL_ rename=ytd1=YTD_LY )
name = month
prefix= ytd;
By RC
GM
SVP
VP
Code_line_item
Line_item
Hierarchy;
run;
data ytd_ly_trans_1;
attrib month format=$CHAR16. ;
set ytd_ly_trans;
run;
proc transpose data=cy out=cy_trans (drop= _LABEL_ rename=actual1=Actual )
name = month
prefix= actual;
By RC
GM
SVP
VP
Code_line_item
Line_item
Hierarchy;
run;
data cy_trans_1;
attrib month format=$CHAR16. ;
set cy_trans;
run;
proc transpose data=ytd_cy out=ytd_cy_trans (drop= _LABEL_ rename=ytd1=YTD_Actual )
name = month
prefix= ytd;
By RC
GM
SVP
VP
Code_line_item
Line_item
Hierarchy;
run;
data ytd_cy_trans_1;
attrib month format=$CHAR16. ;
set ytd_cy_trans;
run;
proc transpose data=plan_cy out=plan_trans_cy_1 (drop= _LABEL_ rename=plan1=Plan )
name = month
prefix= plan;
By RC
GM
SVP
VP
Code_line_item
Line_item
Hierarchy;
run;
proc transpose data=ytd_plan_cy out=ytd_plan_trans_cy_1 (drop= _LABEL_ rename=ytd1=YTD_PLAN )
name = month
prefix= ytd;
By RC
GM
SVP
VP
Code_line_item
Line_item
Hierarchy;
run;
proc sql;
create table ly_out as
select RC,
GM,
SVP,
VP,
Code_line_item,
Line_item,
Hierarchy,
b.date,
LY
from ly_trans_1 a join shared.month_opex_tableau_2017 b
on compress(a.month) = compress(b.month);
create table ytd_ly_out as
select RC,
GM,
SVP,
VP,
Code_line_item,
Line_item,
Hierarchy,
b.date,
ytd_LY
from ytd_ly_trans_1 a join shared.month_opex_tableau_2017 b
on compress(a.month) = compress(b.month);
create table cy_out as
select RC,
GM,
SVP,
VP,
Code_line_item,
Line_item,
Hierarchy,
b.date,
Actual
from cy_trans_1 a join shared.month_opex_tableau_2017 b
on compress(a.month) = compress(b.month);
create table ytd_cy_out as
select RC,
GM,
SVP,
VP,
Code_line_item,
Line_item,
Hierarchy,
b.date,
ytd_Actual
from ytd_cy_trans_1 a join shared.month_opex_tableau_2017 b
on compress(a.month) = compress(b.month);
create table plan_cy_out as
select RC,
GM,
SVP,
VP,
Code_line_item,
Line_item,
Hierarchy,
b.date,
Plan
from plan_trans_cy_1 a join shared.month_opex_tableau_2017 b
on a.month = b.month;
create table ytd_plan_cy_out as
select RC,
GM,
SVP,
VP,
Code_line_item,
Line_item,
Hierarchy,
b.date,
ytd_Plan
from ytd_plan_trans_cy_1 a join shared.month_opex_tableau_2017 b
on a.month = b.month;
quit;
proc sql;
create table all_join as
select a.*, b.*, c.*,d.*,e.*,f.*
from cy_out a join plan_cy_out b
on a.rc=b.rc and a.gm=b.gm and a.svp=b.svp and a.vp=b.vp and a.code_line_item=b.code_line_item and a.line_item=b.line_item and a.hierarchy=b.hierarchy and a.date=b.date
join ly_out c
on a.rc=c.rc and a.gm=c.gm and a.svp=c.svp and a.vp=c.vp and a.code_line_item=c.code_line_item and a.line_item=c.line_item and a.hierarchy=c.hierarchy and a.date=c.date
join ytd_cy_out d
on a.rc=d.rc and a.gm=d.gm and a.svp=d.svp and a.vp=d.vp and a.code_line_item=d.code_line_item and a.line_item=d.line_item and a.hierarchy=d.hierarchy and a.date=d.date
join ytd_ly_out e
on a.rc=e.rc and a.gm=e.gm and a.svp=e.svp and a.vp=e.vp and a.code_line_item=e.code_line_item and a.line_item=e.line_item and a.hierarchy=e.hierarchy and a.date=e.date
join ytd_plan_cy_out f
on a.rc=f.rc and a.gm=f.gm and a.svp=f.svp and a.vp=f.vp and a.code_line_item=f.code_line_item and a.line_item=f.line_item and a.hierarchy=f.hierarchy and a.date=f.date;
quit;
proc sql;
create table op.inter_input_file as
select RC as 'Cost Centre'n,
GM,
SVP,
VP,
Line_item as 'Line Item'n,
Code_line_item as 'Code Line Item'n,
Hierarchy,
date as month,
Actual,
LY,
Plan,
ytd_actual as 'YTD Actual'n,
ytd_ly as 'YTD LY'n,
ytd_plan as 'YTD Plan'n
from all_join;
quit;
/*Waterfall*/
proc sql;
create table waterfall_1 as
select a.*,
(case when 'Line Item'n in ('Travel & Entertainment','Printing & Stationery','Other Operating Expenses',
'Professional Services','Occupancy & Equipment','Communications','Salaries and Employee Benefits') then plan else 0 end) as w_plan,
(case when 'Line Item'n in ('Travel & Entertainment','Printing & Stationery','Other Operating Expenses',
'Professional Services','Occupancy & Equipment','Communications','Salaries and Employee Benefits') then actual else 0 end) as w_actual,
(case when 'Line Item'n in ('Travel & Entertainment','Printing & Stationery','Other Operating Expenses',
'Professional Services','Occupancy & Equipment','Communications','Salaries and Employee Benefits') then 'ytd plan'n else 0 end) as w_ytd_plan,
(case when 'Line Item'n in ('Travel & Entertainment','Printing & Stationery','Other Operating Expenses',
'Professional Services','Occupancy & Equipment','Communications','Salaries and Employee Benefits') then 'ytd actual'n else 0 end) as w_ytd_actual
from op.inter_input_file a
where 'Line Item'n not in ('Regular Payroll','Payroll Costs & Benefits','Outside DP Contractors','Incentive Bonus Provision');
quit;
data waterfall_2;
set waterfall_1;
/*month*/
w_var=w_plan-w_actual;
sizingvar=w_plan-w_actual;
Labelvar=w_plan-w_actual;
runningvar=-(w_plan-w_actual);
/*ytd*/
w_ytdvar=w_ytd_plan-w_ytd_actual;
sizingytdvar=w_ytd_plan-w_ytd_actual;
Labelytdvar=w_ytd_plan-w_ytd_actual;
runningytdvar=-(w_ytd_plan-w_ytd_actual);
run;
/*Line Item level*/
/*Total actual for Line item*/
proc sql;
create table waterfall_3 as
select GM,
SVP,
VP,
'Cost Centre'n,
month,
/*month*/
sum(w_actual) as w_actual,
sum(w_actual) as w_plan,
0 as w_var,
-sum(w_actual) as sizingvar,
sum(w_actual) as labelvar,
0 as runningvar,
/*ytd*/
sum(w_ytd_actual) as w_ytd_actual,
sum(w_ytd_actual) as w_ytd_plan,
0 as w_ytdvar,
-sum(w_ytd_actual) as sizingytdvar,
sum(w_ytd_actual) as labelytdvar,
0 as runningytdvar
from waterfall_2
group by 1,2,3,4,5;
quit;
data waterfall_4;
set waterfall_2 waterfall_3;
run;
data waterfall_5;
set waterfall_4;
if 'Line Item'n = " " then 'Line Item'n = "Total Actual";
run;
/*Total Plan for Line Item*/
proc sql;
create table waterfall_6 as
select GM,
SVP,
VP,
'Cost Centre'n,
month,
/*month*/
sum(w_plan) as w_actual,
sum(w_plan) as w_plan,
sum(w_plan) as w_var,
-sum(w_plan) as sizingvar,
sum(w_plan) as labelvar,
sum(w_plan) as runningvar,
/*ytd*/
sum(w_ytd_plan) as w_ytd_actual,
sum(w_ytd_plan) as w_ytd_plan,
sum(w_ytd_plan) as w_ytdvar,
-sum(w_ytd_plan) as sizingytdvar,
sum(w_ytd_plan) as labelytdvar,
sum(w_ytd_plan) as runningytdvar
from waterfall_2
group by 1,2,3,4,5;
quit;
data waterfall_7;
set waterfall_5 waterfall_6;
run;
data waterfall_8;
set waterfall_7;
if 'Line Item'n = " " then 'Line Item'n = "Total Plan";
run;
/*VP Level*/
/*Total Actual for VP*/
proc sql;
create table waterfall_3_vp as
select GM,
SVP,
month,
/*month*/
sum(w_actual) as w_actual,
sum(w_actual) as w_plan,
0 as w_var,
-sum(w_actual) as sizingvar,
sum(w_actual) as labelvar,
0 as runningvar,
/*ytd*/
sum(w_ytd_actual) as w_ytd_actual,
sum(w_ytd_actual) as w_ytd_plan,
0 as w_ytdvar,
-sum(w_ytd_actual) as sizingytdvar,
sum(w_ytd_actual) as labelytdvar,
0 as runningytdvar
from waterfall_2
group by 1,2,3;
quit;
data waterfall_4_vp;
set waterfall_8 waterfall_3_vp;
run;
data waterfall_5_vp;
set waterfall_4_vp;
if VP = " " then VP = "Total Actual";
run;
/*Total Plan for VP*/
proc sql;
create table waterfall_6_vp as
select GM,
SVP,
month,
/*month*/
sum(w_plan) as w_actual,
sum(w_plan) as w_plan,
sum(w_plan) as w_var,
-sum(w_plan) as sizingvar,
sum(w_plan) as labelvar,
sum(w_plan) as runningvar,
/*ytd*/
sum(w_ytd_plan) as w_ytd_actual,
sum(w_ytd_plan) as w_ytd_plan,
sum(w_ytd_plan) as w_ytdvar,
-sum(w_ytd_plan) as sizingytdvar,
sum(w_ytd_plan) as labelytdvar,
sum(w_ytd_plan) as runningytdvar
from waterfall_2
group by 1,2,3;
quit;
data waterfall_7_vp;
set waterfall_5_vp waterfall_6_vp;
run;
data waterfall_8_vp;
set waterfall_7_vp;
if VP = " " then VP = "Total Plan";
run;
/*SVP Level*/
/*Total Actual for SVP*/
proc sql;
create table waterfall_3_svp as
select GM,
month,
/*month*/
sum(w_actual) as w_actual,
sum(w_actual) as w_plan,
0 as w_var,
-sum(w_actual) as sizingvar,
sum(w_actual) as labelvar,
0 as runningvar,
/*ytd*/
sum(w_ytd_actual) as w_ytd_actual,
sum(w_ytd_actual) as w_ytd_plan,
0 as w_ytdvar,
-sum(w_ytd_actual) as sizingytdvar,
sum(w_ytd_actual) as labelytdvar,
0 as runningytdvar
from waterfall_2
group by 1,2;
quit;
data waterfall_4_svp;
set waterfall_8_vp waterfall_3_svp;
run;
data waterfall_5_svp;
set waterfall_4_svp;
if SVP = " " then SVP = "Total Actual";
run;
/*Total Plan for SVP*/
proc sql;
create table waterfall_6_svp as
select GM,
month,
/*month*/
sum(w_plan) as w_actual,
sum(w_plan) as w_plan,
sum(w_plan) as w_var,
-sum(w_plan) as sizingvar,
sum(w_plan) as labelvar,
sum(w_plan) as runningvar,
/*ytd*/
sum(w_ytd_plan) as w_ytd_actual,
sum(w_ytd_plan) as w_ytd_plan,
sum(w_ytd_plan) as w_ytdvar,
-sum(w_ytd_plan) as sizingytdvar,
sum(w_ytd_plan) as labelytdvar,
sum(w_ytd_plan) as runningytdvar
from waterfall_2
group by 1,2;
quit;
data waterfall_7_svp;
set waterfall_5_svp waterfall_6_svp;
run;
data waterfall_8_svp;
set waterfall_7_svp;
if SVP = " " then SVP = "Total Plan";
run;
/*GM Level*/
/*Total Actual for GM*/
proc sql;
create table waterfall_3_gm as
select
month,
/*month*/
sum(w_actual) as w_actual,
sum(w_actual) as w_plan,
0 as w_var,
-sum(w_actual) as sizingvar,
sum(w_actual) as labelvar,
0 as runningvar,
/*ytd*/
sum(w_ytd_actual) as w_ytd_actual,
sum(w_ytd_actual) as w_ytd_plan,
0 as w_ytdvar,
-sum(w_ytd_actual) as sizingytdvar,
sum(w_ytd_actual) as labelytdvar,
0 as runningytdvar
from waterfall_2
group by 1;
quit;
data waterfall_4_gm;
set waterfall_8_svp waterfall_3_gm;
run;
data waterfall_5_gm;
set waterfall_4_gm;
if GM = " " then GM = "Total Actual";
run;
/*Total Plan for GM*/
proc sql;
create table waterfall_6_gm as
select
month,
/*month*/
sum(w_plan) as w_actual,
sum(w_plan) as w_plan,
sum(w_plan) as w_var,
-sum(w_plan) as sizingvar,
sum(w_plan) as labelvar,
sum(w_plan) as runningvar,
/*ytd*/
sum(w_ytd_plan) as w_ytd_actual,
sum(w_ytd_plan) as w_ytd_plan,
sum(w_ytd_plan) as w_ytdvar,
-sum(w_ytd_plan) as sizingytdvar,
sum(w_ytd_plan) as labelytdvar,
sum(w_ytd_plan) as runningytdvar
from waterfall_2
group by 1;
quit;
data waterfall_7_gm;
set waterfall_5_gm waterfall_6_gm;
run;
data waterfall_8_gm;
set waterfall_7_gm;
if GM = " " then GM = "Total Plan";
run;
/*proc export data=waterfall_8_gm outfile='/amex/ramp/access/bdev_msa/dbaner/Projects/Opex_Tableau_2017/waterfall_all.xlsx' dbms=xlsx replace; */
/*run;*/
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Stephen's feedback*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/;
proc sql;
create table waterfall_9 as
select a.*,
(case when 'Line Item'n in ('Regular Payroll','Payroll Costs & Benefits','Outside DP Contractors',
'Incentive Bonus Provision') then plan else 0 end) as w_plan,
(case when 'Line Item'n in ('Regular Payroll','Payroll Costs & Benefits','Outside DP Contractors',
'Incentive Bonus Provision') then actual else 0 end) as w_actual,
(case when 'Line Item'n in ('Regular Payroll','Payroll Costs & Benefits','Outside DP Contractors',
'Incentive Bonus Provision') then 'ytd plan'n else 0 end) as w_ytd_plan,
(case when 'Line Item'n in ('Regular Payroll','Payroll Costs & Benefits','Outside DP Contractors',
'Incentive Bonus Provision') then 'ytd actual'n else 0 end) as w_ytd_actual
from op.inter_input_file a
where 'Line Item'n in ('Regular Payroll','Payroll Costs & Benefits','Outside DP Contractors',
'Incentive Bonus Provision');
quit;
data waterfall_10;
set waterfall_9;
/*month*/
w_var=w_plan-w_actual;
sizingvar=w_plan-w_actual;
Labelvar=w_plan-w_actual;
runningvar=-(w_plan-w_actual);
/*ytd*/
w_ytdvar=w_ytd_plan-w_ytd_actual;
sizingytdvar=w_ytd_plan-w_ytd_actual;
Labelytdvar=w_ytd_plan-w_ytd_actual;
runningytdvar=-(w_ytd_plan-w_ytd_actual);
run;
/* S&B Line Item level*/
/*Total actual for S&B Line item*/
proc sql;
create table waterfall_11 as
select GM,
SVP,
VP,
'Cost Centre'n,
month,
/*month*/
sum(w_actual) as w_actual,
sum(w_actual) as w_plan,
0 as w_var,
-sum(w_actual) as sizingvar,
sum(w_actual) as labelvar,
0 as runningvar,
/*ytd*/
sum(w_ytd_actual) as w_ytd_actual,
sum(w_ytd_actual) as w_ytd_plan,
0 as w_ytdvar,
-sum(w_ytd_actual) as sizingytdvar,
sum(w_ytd_actual) as labelytdvar,
0 as runningytdvar
from waterfall_10
group by 1,2,3,4,5;
quit;
data waterfall_12;
set waterfall_10 waterfall_11;
run;
data waterfall_13;
set waterfall_12;
if 'Line Item'n = " " then 'Line Item'n = "Total Actual-S&B";
run;
/*Total Plan for Line Item*/
proc sql;
create table waterfall_14 as
select GM,
SVP,
VP,
'Cost Centre'n,
month,
/*month*/
sum(w_plan) as w_actual,
sum(w_plan) as w_plan,
sum(w_plan) as w_var,
-sum(w_plan) as sizingvar,
sum(w_plan) as labelvar,
sum(w_plan) as runningvar,
/*ytd*/
sum(w_ytd_plan) as w_ytd_actual,
sum(w_ytd_plan) as w_ytd_plan,
sum(w_ytd_plan) as w_ytdvar,
-sum(w_ytd_plan) as sizingytdvar,
sum(w_ytd_plan) as labelytdvar,
sum(w_ytd_plan) as runningytdvar
from waterfall_10
group by 1,2,3,4,5;
quit;
data waterfall_15;
set waterfall_13 waterfall_14;
run;
data waterfall_16;
set waterfall_15;
if 'Line Item'n = " " then 'Line Item'n = "Total Plan S&B";
run;
data op.waterfall_17;
set waterfall_8_gm waterfall_16;
run;
proc export data=op.waterfall_17 outfile='/amex/ramp/access/bdev_msa/ngirdhar/Opex/DS/waterfall_feedback.xlsx' dbms=xlsx replace;
run;
THE AUTOMATION STEPS I NEED TO PERFORM ON THE ABLVE MENTIONED CODE IS BELOW:
Dataset Dump_1
- Create date macro variable for LY, Actual and Plan and pass the variable in the YTD calculation such that no manual interference is needed.
Dataset Dump_Format
- Create an excel file to log all the “line items”. - I HAVE ATTACHED THE EXCEL FILE LINE_ITEMS FOR THIS STEP.
- Use Scan in the code to extract “codes”.
Proc SQL Statements
- Create a macro variable and pass LY, Actual and Plan, YTD_LY, YTD_Actual and YTD_Plan as parameters.
- Use macro variable made in the first step to calculate YTD etc.
Proc Transpose Statements
- Create a macro variable and pass LY, Actual and Plan, YTD_LY, YTD_Actual and YTD_Plan as parameters.
- No need to create trans_1. Do the formatting in same step while transposing.
Proc SQL Statements
- Not sure the purpose of this step. This can also be done by using macro variable.
Waterfall 1
- Make these groups in the excel file(step 2) itself and not in the code.
Waterfall and rest
- Create a macro variable to pass VP, SVP and GM as parameters to calculate total actual and total plan.
Waterfall 9
- Create groups in excel file.
