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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.