topic Re: Help with this counter in SAS Procedures

Help with this counter

otis — Mon, 27 Oct 2014 22:53:30 GMT

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!

Re: Help with this counter

art297 — Tue, 28 Oct 2014 00:07:01 GMT

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;

Re: Help with this counter

stat_sas — Tue, 28 Oct 2014 01:25:05 GMT

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;

Re: Help with this counter

art297 — Tue, 28 Oct 2014 02:30:39 GMT

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

Re: Help with this counter

stat_sas — Tue, 28 Oct 2014 03:16:39 GMT

Hi Art,

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

Regards,

Re: Help with this counter

data_null__ — Tue, 28 Oct 2014 03:28:23 GMT

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;

Re: Help with this counter

art297 — Tue, 28 Oct 2014 03:53:43 GMT

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.

Re: Help with this counter

stat_sas — Tue, 28 Oct 2014 04:30:18 GMT

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;

Re: Help with this counter

otis — Tue, 28 Oct 2014 15:58:52 GMT

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

Re: Help with this counter

art297 — Tue, 28 Oct 2014 16:42:53 GMT

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