## Help with this counter

Solved
Occasional Contributor
Posts: 7

# Help with this counter

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!

Accepted Solutions
Solution
‎10-28-2014 12:30 AM
Posts: 1,270

## Re: Help with this counter

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;

All Replies
PROC Star
Posts: 8,170

## Re: Help with this counter

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;

Posts: 1,270

## Re: Help with this counter

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;

PROC Star
Posts: 8,170

## Re: Help with this counter

stat@sas: the sql approach you suggested wouldn't assure that the 3 negative balances were adjacent.

Posts: 1,270

## Re: Help with this counter

Hi Art,

Thanks for the input.  "Group by customer,balance" in proc sql code will make sure adjacent numbers. Please advice.

Regards,

PROC Star
Posts: 8,170

## Re: Help with this counter

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.

Solution
‎10-28-2014 12:30 AM
Posts: 1,270

## Re: Help with this counter

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;

Occasional Contributor
Posts: 7

## Re: Help with this counter

Thank you, stat@sas and Art. Both of your codes worked beautifully. I have saved both codes in my library.

PROC Star
Posts: 8,170

## Re: Help with this counter

FWIW: I'd also save data_null_'s suggested code as, IMHO, it was the most efficient of the three solutions.

Posts: 3,852

## Re: Help with this counter

Your test data is a bit "thin".  This might work but you don't explain what you want well enough for me.

data same;
input Customer :\$1. Month \$    Balance;
cards;
A      201401   100
A      201402   -30
A      201403   -30
A      201404   -30
A      201405   -10
;;;;
run;
data same2;
set same;
by customer balance notsorted;

if first.balance then count=0;

if sign(balance) eq -1 then do;

if not (first.balance and last.balance) then count+1;

if last.balance and count ge 3 then output;

end;

run;
proc print;

run;
🔒 This topic is solved and locked.