Formula calculation

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

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

Posted in reply to sameer112217

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.

View solution in original post


All Replies
Contributor
Posts: 58

Re: Formula calculation

[ Edited ]
Posted in reply to sameer112217

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: 22,874

Re: Formula calculation

Posted in reply to sameer112217

Include your expected output for the sample input.

Frequent Contributor
Posts: 99

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

Posted in reply to sameer112217

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
FORD100OCT15..
FORD100NOV15100.
FORD100DEC15100100
FORD100JAN16100100
FORD100FEB16100100
FORD200MAR16100100
FORD300APR16200100
FORD300MAY16300200
FORD600JUN16300300
FORD300JUL16600300
FORD140AUG16300600
FORD70SEP16140300
FORD700OCT1670140
FORD.NOV1670070
FORD.DEC16.700
FORD.JAN17..
FORD100FEB17..
FORD100MAR17100.
FORD100APR17100100
FORD100MAY17100100
FORD100JUN17100100
FORD100JUL17100100
 
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 ]
Posted in reply to sameer112217

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 306 views
  • 3 likes
  • 4 in conversation