Pick Monthend accounts continuously where balance greater than limits for past 3 months

[BaalaRaaji]

Hi Guys,

I have pasted below sample data to get help on the sas logic.

Question: I need to get the list of all account that  have balance greater than credit limit for continuously past 3 months(90 days).

On the reporting month data(Mar20) i picked all accounts where Balance > Limit ...(around 8k acts out of 1.2million acts)

I again subquery to get all the 8k accounts past 4 months data and sorted by month end .And clauluated the diff filed and flags too.

On this data (test2), I need to pull accounts comapring past 3 months (like for Mar20- comapre Dec19,Jan20,Feb200 that has contionusly negative diff i.e., balance > credit lmit.

data test;
input Accno$3. Month_End $10. Balance 8. Limit 8.;
datalines;
402 31-Dec-19 7978.91 7500
402 31-Jan-20 8127.20 7500
402 29-Feb-20 6902.12 7500
402 31-Mar-20 8369.17 7500
405 31-Dec-19 1712.83 7500
405 31-Jan-20 1762.32 7500
405 29-Feb-20 1762.32 0
405 31-Mar-20 1762.32 0
406 31-Dec-19 7978.91 7500
406 31-Jan-20 8127.20 7500
406 29-Feb-20 6902.12 7500
406 31-Mar-20 8369.17 7500
407 31-Dec-19 8000.00 7500
407 31-Jan-20 8000.00 7500
407 29-Feb-20 8000.01 7500
407 31-Mar-20 8000.01 7500
run;

proc sort data= test; by accno /*Month_End*/; run;
data test2;
set test;
IF Balance > Limit then Flag = 'Y' ;else Flag = 'N';
diff= Balance -Limit;
run;

Output: I should get only 407 accno ...as this account has contionuly negative diff for past 90 days..

any help or advise apprecited.

thanks

[Kurt_Bremser · Accepted solution]

You only have the last 4 months in the dataset, so one entry with balance < limit shall cause the flag to be 'N':

data want;
set have;
by accno;
retain flag;
if first.accno then flag = 'Y';
if balance < limit then flag = 'N';
if last.accno and flag = 'Y';
keep accno flag;
run;

[BaalaRaaji]

The above logic is not working properly...
just need to pull all accounts that have balance greater then Limit for the continuously for the past 3 months...

[Kurt_Bremser]

Per your initial posting, you should only get account 409.

data have;
input Accno :$3. Month_End :date11. Balance Limit;
datalines;
402 31-Dec-19 7978.91 7500
402 31-Jan-20 8127.20 7500
402 29-Feb-20 6902.12 7500
402 31-Mar-20 8369.17 7500
405 31-Dec-19 1712.83 7500
405 31-Jan-20 1762.32 7500
405 29-Feb-20 1762.32 0
405 31-Mar-20 1762.32 0
406 31-Dec-19 7978.91 7500
406 31-Jan-20 8127.20 7500
406 29-Feb-20 6902.12 7500
406 31-Mar-20 8369.17 7500
407 31-Dec-19 8000.00 7500
407 31-Jan-20 8000.00 7500
407 29-Feb-20 8000.01 7500
407 31-Mar-20 8000.01 7500
;

data want;
set have;
by accno;
retain flag;
if first.accno then flag = 'Y';
if balance < limit then flag = 'N';
if last.accno and flag = 'Y';
keep accno flag;
run;

proc print data=want noobs;
run;

Result:

Accno	flag
407	Y

[BaalaRaaji]

Hi Team,

Below is the sample data and i need to get all accounts that have balance > limit continuously past 3 months...

data test;
input Accno$3. Month_End $10. Balance 8. Limit 8.;
datalines;
402 31-Dec-19 7978.91 7500
402 31-Jan-20 8127.20 7500
402 29-Feb-20 6902.12 7500
402 31-Mar-20 8369.17 7500
405 31-Dec-19 1712.83 7500
405 31-Jan-20 1762.32 7500
405 29-Feb-20 1762.32 0
405 31-Mar-20 1762.32 0
406 31-Dec-19 7978.91 7500
406 31-Jan-20 8127.20 7500
406 29-Feb-20 6902.12 7500
406 31-Mar-20 8369.17 7500
407 31-Dec-19 8000.00 7500
407 31-Jan-20 8000.00 7500
407 29-Feb-20 8000.01 7500
407 31-Mar-20 8000.01 7500
run;

[PaigeMiller]

@BaalaRaaji 

Please do not post the same question twice.

data test1;
input Accno$3. Month_End $10. Balance 8. Limit 8.;
prev_acct=lag(Accno);
if Accno^=prev_acct then flag=0;
if balance>limit then flag+1;
else flag=0;
if flag=3 then output;
drop prev_acct;
datalines;
402 31-Dec-19 7978.91 7500
402 31-Jan-20 8127.20 7500
402 29-Feb-20 6902.12 7500
402 31-Mar-20 8369.17 7500
405 31-Dec-19 1712.83 7500
405 31-Jan-20 1762.32 7500
405 29-Feb-20 1762.32 0
405 31-Mar-20 1762.32 0
406 31-Dec-19 7978.91 7500
406 31-Jan-20 8127.20 7500
406 29-Feb-20 6902.12 7500
406 31-Mar-20 8369.17 7500
407 31-Dec-19 8000.00 7500
407 31-Jan-20 8000.00 7500
407 29-Feb-20 8000.01 7500
407 31-Mar-20 8000.01 7500
run;

[Kurt_Bremser]

---

@PaigeMiller wrote:

@BaalaRaaji 

Please do not post the same question twice.

---

Cleaned that up...