topic Re: Formula calculation in SAS Programming

Formula calculation

sameer112217 — Thu, 07 Sep 2017 20:32:53 GMT

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.

Re: Formula calculation

sameer112217 — Thu, 07 Sep 2017 20:39:49 GMT

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

Re: Formula calculation

Reeza — Thu, 07 Sep 2017 21:07:32 GMT

Include your expected output for the sample input.

Re: Formula calculation

MarkWik — Thu, 07 Sep 2017 21:14:58 GMT

@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.

Re: Formula calculation

sameer112217 — Fri, 08 Sep 2017 05:29:23 GMT

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!

Re: Formula calculation

MINX — Fri, 08 Sep 2017 14:55:12 GMT

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.

Re: Formula calculation

sameer112217 — Sat, 09 Sep 2017 15:16:07 GMT

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

Re: Formula calculation

sameer112217 — Sat, 09 Sep 2017 16:06:04 GMT

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.