OPEX_DUMP_JAN2017 is attached :    ... View more

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.       ... View more

Hi, I just want to make this step cut short using a macro variable. Only these columns need to be used. Thanks! ... View more