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.
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.
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
Include your expected output for the sample input.
@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.
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!
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.
Thanks MINX I got the desired output.
SAS Output
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 |
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.