Counting three consecutive month with no transaction

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Counting three consecutive month with no transaction

Hello

 

Suppose I have three variables... account no.... month and transaction amount

 

how can i identify if the person has not made any transaction for more than three months

 

account no

 

i am aware of the formula

 

max(of jan2016-dec2016) eq 0 then broken.. that is one month

 

something like this ... if it finds 0 then give 1 values and increment it by 1 and if it is more than 3 then broken

 


Accepted Solutions
Solution
‎08-10-2017 04:58 AM
PROC Star
Posts: 283

Re: Counting three consecutive month with no transaction

Posted in reply to novinosrin

Some more fun-

 

data account;

input account $ transactionamount    month $;

datalines;

FORD   100                           JAN

FORD   100                           FEB

FORD   100                           MAR

FORD   .                               APR

FORD   .                               MAY

FORD   .                              JUN

FORD   100                           JUL

FORD   100                           AUG

FORD   100                           SEP

FORD   100                           OCT

FORD   100                           NOV

FORD   100                           DEC

TOY      100                           JAN

TOY   100                           FEB

TOY   100                           MAR

TOY   100                           APR

TOY   100                           MAY

TOY   100                          JUN

TOY   100                           JUL

TOY   100                           AUG

TOY   100                           SEP

TOY   100                           OCT

TOY   100                           NOV

TOY   100                           DEC

;

 

data want;

 declare hash myhash();

    myhash.definekey('account','transactionamount');

    myhash.definedata('remark');

    myhash.definedone();

do until(last.account);

do _n_=1 by 1 until(mod(_n_,3)=0);

  set account;

by account notsorted;

if _n_=1 then temp=0;

temp+transactionamount;

if _n_=3 and temp=0 then do;

remark='broken';

myhash.add();

end;

else call missing(remark);

end;

end;

do until(last.account);

set account;

by account notsorted;

if myhash.find() ne 0 then call missing(remark);

output;

end;

drop temp;

run;

 

Regards,

Naveen Srinivasan

View solution in original post


All Replies
PROC Star
Posts: 283

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217

Please post a sample of your input data and a small sample of output data( exactly how you want). Transaction data is a piece of cake. Once I have your req in figures, I will give you the code. It also helps others too. Thanks!

Super Contributor
Posts: 441

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217

I believe you're overthinking the problem - if you sort your data by accountno and month then, in a data step, use the lag function to look back one, two and three periods by accountno then you'll find it much easier. I suspect your solution would run into multiple complexities with a rolling three month span.

 

One thing to be careful of with lag() though is - "don't use it inside conditional statements" or you'll get incorrect results.

 

If you post a datastep to create some sample data and some desired output we can give you a more detailed answer.

Contributor
Posts: 56

Re: Counting three consecutive month with no transaction

Posted in reply to ChrisBrooks

Sample dataset

 

data account;

account  transactionamount    month

FORD   100                           JAN

FORD   100                           FEB

FORD   100                           MAR

FORD   .                               APR

FORD   .                               MAY

FORD   .                              JUN

FORD   100                           JUL

FORD   100                           AUG

FORD   100                           SEP

FORD   100                           OCT

FORD   100                           NOV

FORD   100                           DEC

TOY      100                           JAN

TOY   100                           FEB

TOY   100                           MAR

TOY   100                           APR

TOY   100                           MAY

TOY   100                          JUN

TOY   100                           JUL

TOY   100                           AUG

TOY   100                           SEP

TOY   100                           OCT

TOY   100                           NOV

TOY   100                           DEC

 

I cannot officialy post work dataset but something like this... suppose if a customer has not made transaction for three consecutive months it needs to identified and in new column remark should mention.... broken

 

can someone help me with code. thanks everyone

 

 

 

PROC Star
Posts: 283

Re: Counting three consecutive month with no transaction

[ Edited ]
Posted in reply to sameer112217

data account;

input account $ transactionamount    month $;

datalines;

FORD   100                           JAN

FORD   100                           FEB

FORD   100                           MAR

FORD   .                               APR

FORD   .                               MAY

FORD   .                              JUN

FORD   100                           JUL

FORD   100                           AUG

FORD   100                           SEP

FORD   100                           OCT

FORD   100                           NOV

FORD   100                           DEC

TOY      100                           JAN

TOY   100                           FEB

TOY   100                           MAR

TOY   100                           APR

TOY   100                           MAY

TOY   100                          JUN

TOY   100                           JUL

TOY   100                           AUG

TOY   100                           SEP

TOY   100                           OCT

TOY   100                           NOV

TOY   100                           DEC

;

 

 

 

data want;

do until(last.account);

do _n_=1 by 1 until(mod(_n_,3)=0);

if _n_=1 then temp=0;

set account;

by account notsorted;

temp+transactionamount;

if _n_=3 and temp=0 then remark='broken';

else call missing(remark);

output;

end;

end;

drop temp;

run;

 

 

Solution
‎08-10-2017 04:58 AM
PROC Star
Posts: 283

Re: Counting three consecutive month with no transaction

Posted in reply to novinosrin

Some more fun-

 

data account;

input account $ transactionamount    month $;

datalines;

FORD   100                           JAN

FORD   100                           FEB

FORD   100                           MAR

FORD   .                               APR

FORD   .                               MAY

FORD   .                              JUN

FORD   100                           JUL

FORD   100                           AUG

FORD   100                           SEP

FORD   100                           OCT

FORD   100                           NOV

FORD   100                           DEC

TOY      100                           JAN

TOY   100                           FEB

TOY   100                           MAR

TOY   100                           APR

TOY   100                           MAY

TOY   100                          JUN

TOY   100                           JUL

TOY   100                           AUG

TOY   100                           SEP

TOY   100                           OCT

TOY   100                           NOV

TOY   100                           DEC

;

 

data want;

 declare hash myhash();

    myhash.definekey('account','transactionamount');

    myhash.definedata('remark');

    myhash.definedone();

do until(last.account);

do _n_=1 by 1 until(mod(_n_,3)=0);

  set account;

by account notsorted;

if _n_=1 then temp=0;

temp+transactionamount;

if _n_=3 and temp=0 then do;

remark='broken';

myhash.add();

end;

else call missing(remark);

end;

end;

do until(last.account);

set account;

by account notsorted;

if myhash.find() ne 0 then call missing(remark);

output;

end;

drop temp;

run;

 

Regards,

Naveen Srinivasan

Super User
Posts: 11,343

Re: Counting three consecutive month with no transaction

Posted in reply to novinosrin

Is you data absolutely always going to be from within a single calendar year? If not you will need a year component to compare such things and an actual SAS date value would be better.

Super User
Posts: 7,863

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217

Use lag(), as @ChrisBrooks suggested, and a retained counter to prevent a false alarm in the first 2 obs of an account.

- do a data step with by account;

- at first account, set counter to 1, else increment by 1

- retrieve previous two values by using lag(transactionamount) and lag2(transactionamount)

- if counter > 2 and transactionamount and all the lagged values are missing, set remark to 'broken'

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 56

Re: Counting three consecutive month with no transaction

Posted in reply to KurtBremser
It would be great you can write the code as i never used lag statement
Super Contributor
Posts: 441

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217

I think this should do the trick - note that if FORD for July is missing then flag will also equal 1 for that month - you'll need to amend the code if that isn't what you want

 

data have;
input account $ transactionamount    month $;
datalines;
FORD   100                           JAN
FORD   100                           FEB
FORD   100                           MAR
FORD   .                               APR
FORD   .                               MAY
FORD   .                              JUN
FORD   100                           JUL
FORD   100                           AUG
FORD   100                           SEP
FORD   100                           OCT
FORD   100                           NOV
FORD   100                           DEC
TOY      100                           JAN
TOY   100                           FEB
TOY   100                           MAR
TOY   100                           APR
TOY   100                           MAY
TOY   100                          JUN
TOY   100                           JUL
TOY   100                           AUG
TOY   100                           SEP
TOY   100                           OCT
TOY   100                           NOV
TOY   100                           DEC
;
run;

proc sql;
	create table have_sorted
	as select *
	from have
	order by account,
	case
		when month="JAN" then 1
		when month="FEB" then 2
		when month="MAR" then 3
		when month="APR" then 4
		when month="MAY" then 5
		when month="JUN" then 6
		when month="JUL" then 7
		when month="AUG" then 8
		when month="SEP" then 9
		when month="OCT" then 10
		when month="NOV" then 11
		when month="DEC" then 12
		else 99
	end
	;
quit;

data want;
	set have_sorted;
	by account;
	if first.account then counter=1;
	else counter=counter+1;
	
	lag1=lag1(transactionamount);
	lag2=lag2(transactionamount);
	if missing(transactionamount) and missing(lag1) and missing(lag2) and counter>2 then flag=1;
	else flag=0;
	
	retain counter;
run;
PROC Star
Posts: 1,760

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217

Like this?


data WANT;
  set ACCOUNT;
  if      TRANSACTIONAMOUNT =. 
   & lag (TRANSACTIONAMOUNT)=. 
   & lag2(TRANSACTIONAMOUNT)=. 
   & _N_>3
   & lag (ACCOUNT) = ACCOUNT
   & lag2(ACCOUNT) = ACCOUNT
   then FLAG='Broken';
proc print noobs; run;

 

ACCOUNT TRANSACTIONAMOUNT MONTH FLAG
FORD 100 JAN  
FORD 100 FEB  
FORD 100 MAR  
FORD   APR  
FORD   MAY  
FORD   JUN Broken
FORD 100 JUL  
FORD 100 AUG  
FORD 100 SEP  
FORD 100 OCT  
FORD 100 NOV  
FORD 100 DEC  
TOY 100 JAN  
TOY 100 FEB  
TOY 100 MAR  
TOY 100 APR  
TOY 100 MAY  
TOY 100 JUN  
TOY 100 JUL  
TOY 100 AUG  
TOY 100 SEP  
TOY 100 OCT  
TOY 100 NOV  
TOY 100 DEC  

 

Contributor
Posts: 56

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217
Data is not for single calender year but for last 3 years. By the way all replies are high class and helpful and i tested each reply from every user gave me new insight
Super User
Posts: 11,343

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217

sameer112217 wrote:
Data is not for single calender year but for last 3 years. By the way all replies are high class and helpful and i tested each reply from every user gave me new insight

Then you will need the year variable to sort by within the products as well as the numeric month.

Super User
Posts: 10,046

Re: Counting three consecutive month with no transaction

Posted in reply to sameer112217
The following gave you all the number of adjacent missing value.
It is easy to pick up the number greater than three.



 data have;
input account $ transactionamount    month $;
datalines;
FORD   100                           JAN
FORD   100                           FEB
FORD   100                           MAR
FORD   .                               APR
FORD   .                               MAY
FORD   .                              JUN
FORD   100                           JUL
FORD   100                           AUG
FORD   100                           SEP
FORD   100                           OCT
FORD   100                           NOV
FORD   100                           DEC
TOY      100                           JAN
TOY   100                           FEB
TOY   100                           MAR
TOY   100                           APR
TOY   100                           MAY
TOY   100                          JUN
TOY   100                           JUL
TOY   100                           AUG
TOY   100                           SEP
TOY   100                           OCT
TOY   100                           NOV
TOY   100                           DEC
;
run;
data temp;
 set have;
 by account transactionamount notsorted;
 group+first.transactionamount;
run;
proc sql;
create table want as
 select account,group,count(*) as n_missing
  from temp
    where transactionamount is missing
       group by account,group;
quit;


Contributor
Posts: 56

Re: Counting three consecutive month with no transaction

Thank you everyone. Each reply and solution was unique and high quality material

☑ This topic is solved.

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

Discussion stats
  • 14 replies
  • 284 views
  • 2 likes
  • 7 in conversation