BookmarkSubscribeRSS Feed
SNG1
Calcite | Level 5

Hi All,

 

I have a Data Set which kind of looks like below - 

 

Loan_IDSys_DatePaymentScheduled PaymentFlag
48-Feb-18$135.96$135.96Y
422-Feb-18$135.96$135.96Y
48-Mar-18$135.96$135.96Y
422-Mar-18$135.96$135.96Y
45-Apr-18$135.96$135.96Y
419-Apr-18$135.96$135.96Y
43-May-18$135.96$135.96Y
417-May-18$135.96$135.96Y
431-May-18$135.96$135.96Y
414-Jun-18$135.96$135.96Y
428-Jun-18$135.96$135.96Y
412-Jul-18$135.96$135.96Y
426-Jul-18$4,702.18$135.96Y
515-Feb-18$0.00$137.52N
51-Mar-18$137.52$137.52Y
515-Mar-18$137.52$137.52Y
529-Mar-18$137.52$137.52Y
512-Apr-18$0.00$137.52N
818917-Aug-18$51.44$51.44Y
818931-Aug-18$51.44$51.44Y
818914-Sep-18$0.00$51.44N
818928-Sep-18$51.44$51.44Y
818912-Oct-18$0.00$51.44N
818926-Oct-18$0.00$51.44N
81899-Nov-18$51.44$51.44Y
818915-Nov-18$202.88$51.44Y
818923-Nov-18$51.44$51.44Y
81897-Dec-18$51.44$51.44N
818921-Dec-18$51.44$51.44N
999917-Aug-18$99.30$99.30Y
999931-Aug-18$0.00$99.30N
99995-Sep-18$99.30$99.30Y
999914-Sep-18$99.30$99.30Y
999928-Sep-18$0.00$99.30N
999929-Sep-18$99.30$99.30Y
999912-Oct-18$99.30$99.30Y
999916-Oct-18$49.30$99.30Y
999925-Oct-18($149.30)$99.30N
999926-Oct-18$99.30$99.30Y
111128-Sep-18$50.00$50.00Y
111129-Sep-18$50.00$50.00Y
111112-Oct-18$30.00$50.00N
111116-Oct-18$0.00$50.00N
111125-Oct-18$50.00$50.00Y
111126-Oct-18$0.00$50.00N

 

Loan_ID,

Sys_Date,

Payment (which is the Paid Amount),

Scheduled Payment (which is the scheduled Payment amount for the Loan),

Flag (Y = Successful Payment; N = Unsuccessful Payment).

 

From the data set below I only need the ID's which have 3 consecutive payments (given the condition that PAYMENT >= SCHEDULED_PAYMENT)

 

For Example from the above data I only need to keep the following ID's (Loan_id 4,5 & 8189)

 

I need to keep loan_ID 4 as it meets 3 consecutive Payments (I.E. Each Payment is >= Scheduled Payment and as per Y in the flag field they were all Successful)

 

I need to keep loan_ID 5 AS THIS ALSO HAS 3 CONSECUTIVE PAYMENTS (i.e. Three times FLAG = Y and payment >= scheduled payment)

 

I need to keep 8189 this also has an instance of three consecutive payments (i.e. i.e. Three times FLAG = Y and payment >= scheduled payment)

 

I need to EXCLUDE 9999 as this does not meet my criteria, you can see that even though there are times FLAG = Y  but one of the amounts in Payments ($49.30) is < Scheduled Payment ($99.30) therefore this has to be excluded.

 

i NEED TO EXCLUDE 1111 AS IT DOES NOT MEET THE 3 Consecutive payments criteria

 

I have tried different variations but I am unable to get to the final result which should only Show 

 

Loan_ID
4
5
8189

 

Any help in solving this dilemma is much appreciated!

4 REPLIES 4
ballardw
Super User

You should provide an example of what you expect the actual output data set to look like given that input data. I am not sure if you are expecting 3 records or many more.

 

Also, can you try to clean up your definition of "consecutive". Listing examples doesn't necessarily provide the rule(s) needed.

 

You say:

From the data set below I only need the ID's which have 3 consecutive payments 
(given the condition that PAYMENT >= SCHEDULED_PAYMENT)

but then wander off with Flag values in the examples. So you should data the flag condition a bit more explicitly.

SNG1
Calcite | Level 5

Thanks ballardw for the response - 

 

The actual output data just needs to be the Loan_ID that meets the condition - 

 

3 Consecutive Payments where each payment is >= scheduled payment 

 

I just created the FLAG (i.e. Y or N) so that it would be easier to understand.

 

Basically I only need to keep the Loan_ID that has 3 consecutive Y and for each of the 3 the Payment should be >= scheduled payment

 

So my output should be only the Loan_id'S that satisfy the above and for this data set it is 4,5,8189

 

Hope this helps

Patrick
Opal | Level 21

Below (untested) code should do the job. Source table "have" must be pre-sorted by loan_id sysdate.

data want(keep=loan_id);
  set have;
  by loan_id sys_date;
  if first.loan_id then count=0;
  if count<3 then
    do;
      if flag='Y' and payment>=scheduled_payment then count+1;
      else count=0;
    end;
  if last.loan_id and count=3 then output;
run;
ChrisNZ
Tourmaline | Level 20

No data is given, so this untested, but should do roughly what you want:

data WANT ;   
  COUNT=0;
  do until(last.ID); 
    set HAVE;
    by ID notsorted;
    if FLAG='Y' & PAYMENT >= SCHEDULED then COUNT+1;
    else if COUNT <3 then COUNT=0;              
  end;       
  do until(last.ID); 
    set HAVE;
    by ID notsorted;
    if COUNT >= 3 then output; 
  end;
run;

[Edit: code altered slightly]

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1414 views
  • 2 likes
  • 4 in conversation