topic Re: Counting three consecutive month with no transaction in SAS Programming

Counting three consecutive month with no transaction

sameer112217 — Thu, 03 Aug 2017 23:41:46 GMT

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

Re: Counting three consecutive month with no transaction

novinosrin — Fri, 04 Aug 2017 00:12:18 GMT

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!

Re: Counting three consecutive month with no transaction

ChrisBrooks — Fri, 04 Aug 2017 00:18:44 GMT

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.

Re: Counting three consecutive month with no transaction

sameer112217 — Fri, 04 Aug 2017 03:27:55 GMT

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

Re: Counting three consecutive month with no transaction

novinosrin — Fri, 04 Aug 2017 04:11:01 GMT

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;

drop temp;

run;

Re: Counting three consecutive month with no transaction

novinosrin — Fri, 04 Aug 2017 05:03:11 GMT

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;

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

Re: Counting three consecutive month with no transaction

Kurt_Bremser — Fri, 04 Aug 2017 08:42:20 GMT

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'

Re: Counting three consecutive month with no transaction

sameer112217 — Fri, 04 Aug 2017 09:31:13 GMT

It would be great you can write the code as i never used lag statement

Re: Counting three consecutive month with no transaction

ChrisBrooks — Fri, 04 Aug 2017 09:40:55 GMT

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;

Re: Counting three consecutive month with no transaction

ballardw — Fri, 04 Aug 2017 13:52:43 GMT

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.

Re: Counting three consecutive month with no transaction

sameer112217 — Fri, 04 Aug 2017 15:30:55 GMT

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

Re: Counting three consecutive month with no transaction

ballardw — Fri, 04 Aug 2017 17:50:20 GMT

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

Re: Counting three consecutive month with no transaction

Ksharp — Sat, 05 Aug 2017 11:59:01 GMT

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;

Re: Counting three consecutive month with no transaction

ChrisNZ — Mon, 07 Aug 2017 03:06:10 GMT

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

Re: Counting three consecutive month with no transaction

sameer112217 — Thu, 10 Aug 2017 08:59:40 GMT

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