topic Re: Nr months with neg balance in row in SAS Programming

Nr months with neg balance in row

Ronein — Sat, 21 Dec 2024 20:33:43 GMT

Hello
For each customer I have 12 rows (12 months data) with information of current account balance.
I want to calculate for each customer: Maximum number of months with negative balance in a row (It means that month after month had negative balance).
In this example:
Customer 111 has 3 months in row with negative balance
Customer 222 has 0 months in row with negative balance
What is the way to calculate it in sas please ?
Please see a code that calculate it well but maybe there is other nice code ?

data have;
input CustID YYYYMM balance;
IF balance<0 then Ind_Neg=1;else Ind_Neg=0;
cards;
111 2020401 1000
111 2020402 -4000
111 2020403 1000
111 2020404 1900
111 2020405 -1000
111 2020406 -2000
111 2020407 5000
111 2020408 6000
111 2020409 -2000
111 2020410 -3000
111 2020411 -4000
111 2020412 8000
222 2020401 3000
222 2020402 4000
222 2020403 1000
222 2020404 5000
222 2020405 8000
222 2020406 7800
222 2020407 5000
222 2020408 3000
222 2020409 1000
222 2020410 2000
222 2020411 1000
222 2020412 7000
;
Run;

proc sort data=have;
by CustID YYYYMM; 
run;
data want1;
set have;
by CustID;
retain Accum_Nr_Neg;
if first.CustID then Accum_Nr_Neg=Ind_Neg;
else Accum_Nr_Neg+Ind_Neg;
run;
data want2;
set have;
by CustID;
retain Accum_Nr_Neg_in_Row;
if first.CustID then Accum_Nr_Neg_in_Row=Ind_Neg;
else Accum_Nr_Neg_in_Row=sum(Accum_Nr_Neg_in_Row,Ind_Neg)*Ind_Neg;
run;
proc sql;
create table want3 as
select CustID,
       max(Accum_Nr_Neg_in_Row) as max_Accum_Nr_Neg_in_Row
from want2
group by CustID
;
quit;

Re: Nr months with neg balance in row

PaigeMiller — Sat, 21 Dec 2024 21:03:13 GMT

data want;
    set have;
    by custid;
    if first.custid or balance>=0 then consec=0;
    if balance<0 then consec+1;
run;

Then use PROC SUMMARY to find the max of CONSEC by CUSTID.

Re: Nr months with neg balance in row

Patrick — Sun, 22 Dec 2024 08:55:54 GMT

Below should work.

data want;
	set have;
	by CustID YYYYMM;
	retain max_min cons_min;
	if balance>=0 then cons_min=0;
	else cons_min + 1;
	max_min=max(max_min,cons_min);
	if last.CustID then
		do;
			output;
			call missing(cons_min, max_min);
		end;
	keep CustID max_min;
run;

Re: Nr months with neg balance in row

mkeintz — Thu, 26 Dec 2024 03:32:30 GMT

Once the data are sorted by custid yyyymm (and no gaps in YYYYMM), then a RETAIN statement provides a nice way to dynamically update the maximum consecutive qualifying balances, and output the result for each custid.

data want (keep=custid max_consec);
  set have;
  by custid yyyymm;
  retain max_consec;

  if first.custid=1 or balance>0 then call missing(consec,max_consec);
  if sign(balance)=-1 then consec+1;
  else consec=0;
  max_consec=max(max_consec,consec);
  if last.custid;
run;

Re: Nr months with neg balance in row

ballardw — Thu, 02 Jan 2025 02:07:02 GMT

Numeric variable not a date value. 7 digits. So which of 2020401 is the "month".

With out a very clear definition of what that variable YYYYMM represents and how I wouldn't touch of this with code.

@Ronein wrote:

Hello
For each customer I have 12 rows (12 months data) with information of current account balance.
I want to calculate for each customer: Maximum number of months with negative balance in a row (It means that month after month had negative balance).
In this example:
Customer 111 has 3 months in row with negative balance
Customer 222 has 0 months in row with negative balance
What is the way to calculate it in sas please ?
Please see a code that calculate it well but maybe there is other nice code ?
data have;
input CustID YYYYMM balance;
IF balance<0 then Ind_Neg=1;else Ind_Neg=0;
cards;
111 2020401 1000
111 2020402 -4000
111 2020403 1000
111 2020404 1900
111 2020405 -1000
111 2020406 -2000
111 2020407 5000
111 2020408 6000
111 2020409 -2000
111 2020410 -3000
111 2020411 -4000
111 2020412 8000
222 2020401 3000
222 2020402 4000
222 2020403 1000
222 2020404 5000
222 2020405 8000
222 2020406 7800
222 2020407 5000
222 2020408 3000
222 2020409 1000
222 2020410 2000
222 2020411 1000
222 2020412 7000
;
Run;

proc sort data=have;
by CustID YYYYMM; 
run;
data want1;
set have;
by CustID;
retain Accum_Nr_Neg;
if first.CustID then Accum_Nr_Neg=Ind_Neg;
else Accum_Nr_Neg+Ind_Neg;
run;
data want2;
set have;
by CustID;
retain Accum_Nr_Neg_in_Row;
if first.CustID then Accum_Nr_Neg_in_Row=Ind_Neg;
else Accum_Nr_Neg_in_Row=sum(Accum_Nr_Neg_in_Row,Ind_Neg)*Ind_Neg;
run;
proc sql;
create table want3 as
select CustID,
       max(Accum_Nr_Neg_in_Row) as max_Accum_Nr_Neg_in_Row
from want2
group by CustID
;
quit;

  

 