Help using Base SAS procedures

Help with this counter

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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
Trusted Advisor
Posts: 1,204

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;

View solution in original post


All Replies
PROC Star
Posts: 7,362

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;

Trusted Advisor
Posts: 1,204

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: 7,362

Re: Help with this counter

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

Trusted Advisor
Posts: 1,204

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: 7,362

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
Trusted Advisor
Posts: 1,204

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: 7,362

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.

Respected Advisor
Posts: 3,777

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 376 views
  • 6 likes
  • 4 in conversation