I need to count how many accounts having the same negative balance 3 or more months in a row in year 2014.
Example:
Customer Month Balance Count
A 201401 100 0
A 201402 -30 1
A 201403 -30 2
A 201404 -30 3
A 201405 -10 0
Can someone advise how to code it? Thank you!
Hi Art,
Thanks again for your feedback. Right, it will not pick adjacent balances. I've created a new variable flag that will group adjacent balances.
Regards,
proc sort data=have;
by customer;
run;
data want;
set have;
by customer balance notsorted;
if first.customer then flag=0;
if first.balance then flag+1;
run;
proc sql;
select distinct customer from want
where balance < 0
group by customer,flag
having range(balance)=0
and count(balance)>=3;
quit;
data want (keep=customer);
retain counter .
retain keep;
array stack {0:2};
retain stack:;
set have;
by customer;
if first.customer then do;
call missing(of stack{*});
counter=0;
keep=0;
end;
counter+1;
stack{mod(counter,3)} = Balance;
if counter gt 2 then if stack{0}< 0 and
(stack{0}=stack{1}=stack{2}) then keep=1;
if last.customer and keep then output;
run;
proc sql;
select count(*) as total
from want
;
quit;
Another way using sql.
proc sql;
select customer from have
where balance<0
group by customer,balance
having range(balance)=0
and count(balance)>=3;
quit;
stat@sas: the sql approach you suggested wouldn't assure that the 3 negative balances were adjacent.
Hi Art,
Thanks for the input. "Group by customer,balance" in proc sql code will make sure adjacent numbers. Please advice.
Regards,
No, I don't think so. Group by customer balance will simply put all of the similar balances together regardless of whether they were, or weren't, adjacent in the first place.
Hi Art,
Thanks again for your feedback. Right, it will not pick adjacent balances. I've created a new variable flag that will group adjacent balances.
Regards,
proc sort data=have;
by customer;
run;
data want;
set have;
by customer balance notsorted;
if first.customer then flag=0;
if first.balance then flag+1;
run;
proc sql;
select distinct customer from want
where balance < 0
group by customer,flag
having range(balance)=0
and count(balance)>=3;
quit;
Thank you, stat@sas and Art. Both of your codes worked beautifully. I have saved both codes in my library.
FWIW: I'd also save data_null_'s suggested code as, IMHO, it was the most efficient of the three solutions.
Your test data is a bit "thin". This might work but you don't explain what you want well enough for me.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.