Solved
Contributor
Posts: 58

# Formula calculation

[ Edited ]

data account;

input account \$ transactionamount    month \$;

datalines;

FORD   100                           OCT15

FORD   100                           NOV15

FORD   100                           DEC15

FORD   100                           JAN16

FORD   100                           FEB16

FORD   200                          MAR16

FORD   300                              APR16

FORD   300                              MAY16

FORD   600                            JUN16

FORD   300                           JUL16

FORD   140                          AUG16

FORD    150                           SEP16

FORD    700                           OCT16

FORD   300                           NOV16

FORD   140                           DEC16

FORD    60                           JAN17

FORD    100                           FEB17

FORD    100                           MAR17

FORD   100                           APR17

FORD   .                           MAY17

FORD   .                          JUN17

FORD   .                          JUL17

TOYOTA .(same exercise)

I am stuck in this in one of my project I need help. This one is tough to crack. Suppose I have above sample dataset. It is precisely very similar on this line after I transpose the dataset it look like the above for all the accounts. I want three conditions to be fulfilled. Against every account the data starts from jan 2016 to july 2017.

Condition 1 (Three months consecutive loss)

Suppose if there are no transaction for three consecutive months I need to this comment in another variable called Reason "Three months consecutive loss. In the above case it would be next to the month JULY17

Condition 2 (3 months consecutive  decline means from oct16-jan17 there is gradually 50% decline every month )

Lets start with first example Jan2017. It would take 3 months back. JAN17_R= (JAN17 -DEC16)/DEC16

Dec16_R=(DEC16-NOV16/NOV16

Nov16_R= (NOV16-OCT16/OCT16

if jan17_R<=.5 and dec16_R <=.5 and Nov16_R<=.5 then do;

reason = "50% Monthon month Decline"

It woud give against the month jan17 the reason "50%MOM Decline

Condition 3 (Four Months consecutive Year on Year decline -75% )

Suppose for Jan17example it would take previous three months Oct16, Nov 16, Dec 16 and compare it with 2015 years which is year month back (YOY)  which will be jan 16, Dec15, Nov15, Oct15 for comparison

JAN17_R= (JAN17 -JAN16)/JAN16

Dec16_R=(DEC16-DEC15/DEC15

Nov16_R= (NOV16-NOV15/NOV15

OCT16_R=(OCT16-OCT15)/OCT15

if jan17_R<=.75 and dec16_R <=.75 and Nov16_R<=.75 and oct16_R <.75 then do;

It means if there is 75% decline for 4 consecutive  month on YOY basis.

IF tthe above case is correct it would put this comment "75% decline YOY" against the month jan 2017

I want to evaluate and imply these these three condition starting from jan2017 to july 2017 for every account. Previous months are for computational purpose. Please help me with code.

Accepted Solutions
Solution
‎09-09-2017 11:11 AM
Contributor
Posts: 29

## Re: Formula calculation

Hi,

First, you should add new columns to separate Year and Month. This would be easy for Transpose and calculation.

For condition1 and 2, you can use  look-Ahead and Look-Back method for previous month by using LAG function from http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back, then apply conditions.

I did a macro for your reference:

```%macro prevmonth(ds=,prevn=);

data &ds._prev1;
set &ds.;
by account yr mt;
set &ds. (keep = amount);
Prev1_amount = lag(amount);
run;

%do i = 2 %to &prevn.;
data &ds._prev&i.;
set &ds._prev%eval(&i.-1);
by account yr mt;
set &ds._prev%eval(&i.-1) (keep = Prev%eval(&i.-1)_amount );
Prev&i._amount = lag(Prev%eval(&i.-1)_amount);
run;
%end;

data final_&ds.;
merge &ds._:;
by account yr mt;
run;

%mend prevmonth;

%prevmonth(ds=account1,prevn=3);```

For condtion3, you can transpose value per year and then calculate ratio between years. Then transpose back and merge back with original dataset. After that, perform the similar method above to apply conditions.

All Replies
Contributor
Posts: 58

## Re: Formula calculation

[ Edited ]

In simple first condition is 3 consecutive months with no transaction

Second condition is comparing last 3 months transacton and seeing whether there is continous decline of 50% . If it is jan17..start from oct16 and calculate if decline is from oct16 to jan17

Third condition is YOY calculation- year on year calculation

if it is jan17 go back three months oct16, nov16, dec16 and jan17 and compare the value with 1 year back for all these months..like compare jan17 to jan16, dec16 to dec15, nov16 to nov15 and oct16 to oct15 and check whethere there is 75% decline ...

The month start i need is current year jan17 till jul17. please let me know of issue and code of these conditons woul be helpful

Super User
Posts: 23,951

## Re: Formula calculation

Include your expected output for the sample input.

Frequent Contributor
Posts: 105

## Re: Formula calculation

@sameer112217   Please show the community your attempt and effort to write the code. I'm sure somebody may give you the solution however it is extremely unethical and unfair to get others to do your work.

Contributor
Posts: 58

## Re: Formula calculation

Hello Mark!

I will definitely write a code and give output. I am eager to know from which country you are from. I am curious. Thanks!

Solution
‎09-09-2017 11:11 AM
Contributor
Posts: 29

## Re: Formula calculation

Hi,

First, you should add new columns to separate Year and Month. This would be easy for Transpose and calculation.

For condition1 and 2, you can use  look-Ahead and Look-Back method for previous month by using LAG function from http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back, then apply conditions.

I did a macro for your reference:

```%macro prevmonth(ds=,prevn=);

data &ds._prev1;
set &ds.;
by account yr mt;
set &ds. (keep = amount);
Prev1_amount = lag(amount);
run;

%do i = 2 %to &prevn.;
data &ds._prev&i.;
set &ds._prev%eval(&i.-1);
by account yr mt;
set &ds._prev%eval(&i.-1) (keep = Prev%eval(&i.-1)_amount );
Prev&i._amount = lag(Prev%eval(&i.-1)_amount);
run;
%end;

data final_&ds.;
merge &ds._:;
by account yr mt;
run;

%mend prevmonth;

%prevmonth(ds=account1,prevn=3);```

For condtion3, you can transpose value per year and then calculate ratio between years. Then transpose back and merge back with original dataset. After that, perform the similar method above to apply conditions.

Contributor
Posts: 58

## Re: Formula calculation

Thanks MINX I got the desired output.

SAS Output

Obs account amount mt Prev1_amount Prev2_amount 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
 FORD 100 OCT15 . . FORD 100 NOV15 100 . FORD 100 DEC15 100 100 FORD 100 JAN16 100 100 FORD 100 FEB16 100 100 FORD 200 MAR16 100 100 FORD 300 APR16 200 100 FORD 300 MAY16 300 200 FORD 600 JUN16 300 300 FORD 300 JUL16 600 300 FORD 140 AUG16 300 600 FORD 70 SEP16 140 300 FORD 700 OCT16 70 140 FORD . NOV16 700 70 FORD . DEC16 . 700 FORD . JAN17 . . FORD 100 FEB17 . . FORD 100 MAR17 100 . FORD 100 APR17 100 100 FORD 100 MAY17 100 100 FORD 100 JUN17 100 100 FORD 100 JUL17 100 100

Based on the intial output I can write my conditions like

if amount LE 0  and prev1_amount LE 0  and prev2_amount LE 0  then do;

lost_reason = 'Zero billing three consecutive months';

end;

Similary the other two condtions also I can write if I extend to prev15_month...I got it thanks minx
Contributor
Posts: 58

## Re: Formula calculation

[ Edited ]

Mark this is for you. I have found another logic to write this. Hope you will find this ethical now and wont find me invlving others to write code for me.

prev_bill1=lag1(transactionamount);
prev_bill2=lag2(transactionamount);
prev_bill3=lag3(transactionamount);
prev_bill4=lag4(transactionamount);
prev_bill5=lag5(transactionamount);
prev_bill6=lag6(transactionamount);
prev_bill7=lag7(transactionamount);
prev_bill8=lag8(transactionamount);
prev_bill9=lag9(transactionamount);
prev_bill10=lag10(transactionamount);
prev_bill11=lag11(transactionamount);
prev_bill12=lag12(transactionamount);
prev_bill13=lag13(transactionamount);
prev_bill14=lag14(transactionamount);
prev_bill15=lag15(transactionamount);

if record = 1 then prev_bill1=0;
if record le 2 then prev_bill2=0;
if record le 3 then prev_bill3=0;
if record le 4 then prev_bill4=0;
if record le 5 then prev_bill5=0;
if record le 6 then prev_bill6=0;
if record le 7 then prev_bill7=0;
if record le 8 then prev_bill8=0;
if record le 9 then prev_bill9=0;
if record le 10 then prev_bill10=0;
if record le 11 then prev_bill11=0;
if record le 12 then prev_bill12=0;
if record le 13 then prev_bill13=0;
if record le 14 then prev_bill14=0;
if record le 15 then prev_bill15=0;
run;

We can use the counter for record

if first. account then record =0

then

record +1

Later use the three conditions.

It will create same output.

☑ This topic is solved.