topic macro variables in All Things Community

macro variables

Niharika — Mon, 06 Mar 2017 13:23:20 GMT

PURPOSE: Need to cutshort the program by using a macro variable for the below prog:

QUESTION: How to create a macro variable LY wherein we can get the result of the dataset op.dump1 created below?

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);

run;

Thankyou!

Re: macro variables

Shmuel — Mon, 06 Mar 2017 13:43:00 GMT

Why do you need a macro variable ? What do you intend to do :

Your code maybe shorten by using array and loop:

data op.dump_1;
set op.dump;

array ytd {12} 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;

array sumin {12) Jan2016 Feb2016 Mar2016 Apr2016 May2016 Jun2016

Jul2016 Aug2016 Sep2016 Oct2016 Nov2016 Dec2016;

do i=1 to 12;

do j=1 to i;

ytd(i) = sum(of ytd(i), sumin(j));

end;

run;

Re: macro variables

Kurt_Bremser — Mon, 06 Mar 2017 13:47:20 GMT

Without knowing the struclture and contents of op.dump, we can't tell what the "results" would be, as there could be much more columns than those used in the code.

And then the question remains, which of the columns need to be included, and in what fashion?

Re: macro variables

ballardw — Mon, 06 Mar 2017 15:33:07 GMT

Since your result data set is going to have at least 24 variables what would you want in the the macro variable?

And of course, how is that varaible to be used?

Re: macro variables

Niharika — Mon, 06 Mar 2017 17:45:35 GMT

Hi,

I just want to make this step cut short using a macro variable. Only these columns need to be used.

Thanks!

Re: macro variables

ballardw — Mon, 06 Mar 2017 20:09:43 GMT

@Niharika wrote:
Hi,

I just want to make this step cut short using a macro variable. Only these columns need to be used.

Thanks!

Provide some example data and what the desired final result is supposed to be. I do not seen any way that a single macro variable is going to be much help. Depending on what you are actually wanting to accomplish there could very well be non-macro approach.

Re: macro variables

Shmuel — Wed, 08 Mar 2017 18:06:52 GMT

Please don't post private message.

I quote your letter:

Hi Shmuel,
 
Your answer to my below question is helpful, but still i want to ask if we can get this done through a macro variable?
QUESTION: 
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);
run;
 
I am asking this becz..in the later part of the program, I need to use this Macro variable to calculate the YTD. The intent is to cut short the complete program step by step, so the next step is for making the PROC SQL short: 

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;
 
The Question for cutting short the PROC SQL statement is as below: 
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.
Please help!! Thanks in advance Smiley Happy
Reply

First step creates op. Dump_1.

Where from is your input op.dump_format?

Do you want a macro program with arguments like year, month, type?

Otherwise, what macro variables you want?

Please clarify your needs

Re: macro variables

Niharika — Thu, 09 Mar 2017 11:05:27 GMT

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

SVP

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

SVP

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

SVP

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

SVP

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

SVP

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

SVP

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,

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.

Re: macro variables

Niharika — Thu, 09 Mar 2017 11:06:56 GMT

OPEX_DUMP_JAN2017 is attached :

Re: macro variables

Shmuel — Thu, 09 Mar 2017 16:09:57 GMT

@Niharika, the forum here is to help, show solutions, teach but not to do a full work.

Your code is long, many lines. To shorten the code you have:

1) Learn to create and use macro programming

2) Learn to define macro variables (by %let or call symput() function) and use them (as &macro_var)

3) Use arrays and loops as I have done in my code.

If it is too dificult for you, find someone to do the job - may be an employee or a hired one.

Next code shows how to create and run a macro program, to use arrays and loop in order to create

the various calculations in your first step. I'll show you how to use the macro for 2 different calculations

and you will have to multiple usage to create the other sets.

%let year = 2016;
%let months = Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec;

/* macro to create list of variables per month for array definition */
%macro array_list(arr_name,prefix, suffix);
         %global &arr_name;
         %let &arr_name = ;   /* initiate as empty */
         %do i=1 %to 12;
               %let &arr_name = %trim(&&&arr_name) &Prefix.%scan(&months , &i)&suffix;
         %end;
%mend array_list;
%array_list(arr_ytd_ly, ytd_ly_);  /* no suffix */
%array_list(arr_sum_mm, ,&year);   /* no prefix */
... add here creation of other array lists you need ....

/* check arrays created */
%put ARR_YTD_LY = &arr_ytd_ly;
%put ARR_SUM_MM = &arr_sum_mm;

How to use it in your first step. Next is a conitinuation to the previous code box:

data op.dump_1;
set op.dump;
      array ytd {12} &arr_ytd_ly;
      array sumin {12) &arr_sum_mm;
      ... add here more arrays you need and created ...
      do i=1 to 12;
           do j=1 to i;
               ytd(i) = sum(of ytd(i), sumin(j));
           end;
      end;
run;

If you have specific problems or questions post them to the forum as new post.