cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

SAS EG Programming for Loan Performance

Posted 04-09-2024 10:57 AM (2114 views)

Good morning – I have basic knowledge of SAS EG. I usually make minor edits on existing program but I am trying to understand early delinquency rate by cohorts and have no idea how to go about it.  

I have the following two tables and data fields:

Performance Table 

  • loan_nbr : Unique identifier for each mortgage. Datatype varchar2.
  • entity_id : The code that identifies the entity. Datatype varchar2.
  • mthly_activity_dte :  As of date. Datatype is char (YYYYMM).
  • active_flag : Flag to identify if the loan is in the entity's current book of business. A = Active N = Not Active.
  • loan_status_cde : The status of the mortgage.  For active or delinquent mortgages, it is the status as of the mthly_activity_dte.
    • 00 = Current
    • 01 = 30 - 59 days delinquent
    • 02 = 60 - 89 days delinquent
    • 03 = 90 - 119 days delinquent
    • 04 = 120 - 179 days delinquent
    • 05 = 180+ days delinquent
    • 06 = In Foreclosure
  • cur_upb_amt : The outstanding unpaid principal balance of the loan as of the mthly_activity_dte.
  • lpi_dte : The date of the last paid installment for the loan. Datatype is char (YYYYMMDD).

WLM table

  • loan_nbr : Unique identifier for each mortgage. Datatype varchar2.
  • entity_id : The code that identifies the entity. Datatype varchar2.
  • first_pmt_dte : The date of the first scheduled mortgage payment to be made by the borrower under the terms of the mortgage. Datatype is char (YYYYMMDD).
  • orig_dte : The date the mortgage was originated. Datatype is char (YYYYMMDD).

I am trying to pull the rate of loans that ever went 90+ delinquent (loan_status_cde = 03, 04, 05, 06) for the first year by month since first payment date. Similar to the table below.

Origination Year

Months Since First Payment Due Date

 

1

2

3

4

5

6

7

8

9

10

11

12

 

Year

 

 

 

 

 

 

 

 

 

 

 

 

 

Year

 

 

 

 

 

 

 

 

 

 

 

 

 

Year

 

 

 

 

 

 

 

 

 

 

 

 

 

Year

 

 

 

 

 

 

 

 

 

 

 

 

 

 

0 Likes
Reply
11 REPLIES 11
SASKiwi
PROC Star SASKiwi
PROC Star

Re: SAS EG Programming for Loan Performance

Posted 04-09-2024 04:14 PM (2045 views)  |   In reply to WorkUser

This doesn't look too difficult, but you'll need to supply some made up sample data for us to program with.  We would only need up to 5 loans, plus maybe 2 years of history by month. Please supply this in a DATA step using the DATALINES statement.

 

Not sure why all your dates are character as every banking database I use has dates stored as numbers so you can easily to date calculations. 

0 Likes
Reply
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

Re: SAS EG Programming for Loan Performance

Posted 04-10-2024 08:04 AM (1998 views)  |   In reply to SASKiwi

Thank you for the reply. Hopefully attached sample would work. Not to familiar with DATALINES. 

0 Likes
Reply
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

Re: SAS EG Programming for Loan Performance

Posted 04-17-2024 08:02 AM (1886 views)  |   In reply to WorkUser

@SASKiwi sorry to bother you again but was wondering if you had the opportunity to review the sample dataset. Thanks 

0 Likes
Reply
SASKiwi
PROC Star SASKiwi
PROC Star

Re: SAS EG Programming for Loan Performance

Posted 04-17-2024 03:54 PM (1868 views)  |   In reply to WorkUser

@WorkUser - I'm unable to open your attached spreadsheet as I would be breaking my company's security rules. Please supply using DATALINES like this:

data me1;
   input IDnum $ Jobcode $ Salary Bonus;
   datalines;
1400   ME1         29769  587
1403   ME1         28072  342
1120   ME1         28619  986
1120   ME1         28619  986
;
run;
0 Likes
Reply
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

Re: SAS EG Programming for Loan Performance

Posted 04-18-2024 08:12 AM (1846 views)  |   In reply to SASKiwi

@SASKiwi hope i got it right. 

just and FYI the data i am working with sometimes have empty fields so in the datalines i put "null".

data me1;
  input LOAN_NBR $10. SUBMITTING_ENTITY_ID $ ORIG_DTE $ MTHLY_ACTIVITY_DTE $6. LOAN_STATUS_CDE $ CUR_UPB_AMT :comma. FIRST_PMT_DTE $ LPI_DTE $;
  datalines;
1234567890 ABC 20200101 202001 00 164780.00 20200301 null
1234567890 ABC 20200101 202002 00 164780.00 20200301 null
1234567890 ABC 20200101 202003 00 164581.64 20200301 20200301
1234567890 ABC 20200101 202004 00 164382.46 20200301 20200401
1234567890 ABC 20200101 202005 00 164182.45 20200301 20200501
1234567890 ABC 20200101 202006 00 163981.61 20200301 20200601
1234567890 ABC 20200101 202007 00 163779.93 20200301 20200701
1234567890 ABC 20200101 202008 00 163577.41 20200301 20200801
1234567890 ABC 20200101 202009 00 163374.05 20200301 20200901
1234567890 ABC 20200101 202010 00 163169.84 20200301 20201001
1234567890 ABC 20200101 202011 01 163169.84 20200301 20201001
1234567890 ABC 20200101 202012 01 162964.78 20200301 20201101
1234567890 ABC 20200101 202101 02 162964.78 20200301 20201101
1234567890 ABC 20200101 202102 03 162964.78 20200301 20201101
1234567890 ABC 20200101 202103 03 162758.87 20200301 20201201
1234567890 ABC 20200101 202104 04 162758.87 20200301 20201201
1234567890 ABC 20200101 202105 04 162552.11 20200301 20210101
1234567890 ABC 20200101 202106 04 162344.49 20200301 20210201
1234567890 ABC 20200101 202107 04 162136.00 20200301 20210301
1234567890 ABC 20200101 202108 04 161926.65 20200301 20210401
1234567890 ABC 20200101 202109 04 161716.42 20200301 20210501
1234567890 ABC 20200101 202110 00 165301.16 20200301 20211001
1234567890 ABC 20200101 202111 00 165192.56 20200301 20211101
1234567890 ABC 20200101 202112 00 165083.51 20200301 20211201
1234567890 ABC 20200101 202201 00 164974.00 20200301 20220101
1234567890 ABC 20200101 202202 01 164974.00 20200301 20220101
1234567890 ABC 20200101 202203 01 164864.04 20200301 20220201
1234567890 ABC 20200101 202204 01 164753.62 20200301 20220301
1234567890 ABC 20200101 202205 02 164753.62 20200301 20220301
1234567890 ABC 20200101 202206 03 164753.62 20200301 20220301
1234567890 ABC 20200101 202207 03 164642.74 20200301 20220401
1234567890 ABC 20200101 202208 04 164642.74 20200301 20220401
1234567890 ABC 20200101 202209 04 164642.74 20200301 20220401
1234567890 ABC 20200101 202210 05 164642.74 20200301 20220401
1234567890 ABC 20200101 202211 00 163738.95 20200301 20221201
1234567890 ABC 20200101 202212 00 163623.85 20200301 20230101
1234567890 ABC 20200101 202301 00 163508.27 20200301 20230201
1234567890 ABC 20200101 202302 24 0.00 20200301 20230201
2345678912 ABC 20200101 202001 00 623500.00 20200301 null
2345678912 ABC 20200101 202002 00 623500.00 20200301 null
2345678912 ABC 20200101 202003 00 622678.95 20200301 20200301
2345678912 ABC 20200101 202004 00 621854.82 20200301 20200401
2345678912 ABC 20200101 202005 00 621027.60 20200301 20200501
2345678912 ABC 20200101 202006 00 620197.27 20200301 20200601
2345678912 ABC 20200101 202007 00 619363.83 20200301 20200701
2345678912 ABC 20200101 202008 00 618527.26 20200301 20200801
2345678912 ABC 20200101 202009 00 617687.56 20200301 20200901
2345678912 ABC 20200101 202010 00 616844.71 20200301 20201001
2345678912 ABC 20200101 202011 00 615998.70 20200301 20201101
2345678912 ABC 20200101 202012 00 614297.15 20200301 20210101
2345678912 ABC 20200101 202101 00 613441.58 20200301 20210201
2345678912 ABC 20200101 202102 00 612582.81 20200301 20210301
2345678912 ABC 20200101 202103 00 611520.82 20200301 20210401
2345678912 ABC 20200101 202104 00 608933.74 20200301 20210601
2345678912 ABC 20200101 202105 00 608933.74 20200301 20210601
2345678912 ABC 20200101 202106 00 605476.47 20200301 20210801
2345678912 ABC 20200101 202107 00 603587.83 20200301 20210901
2345678912 ABC 20200101 202108 00 603587.83 20200301 20210901
2345678912 ABC 20200101 202109 00 603587.83 20200301 20210901
2345678912 ABC 20200101 202110 00 602642.10 20200301 20211001
2345678912 ABC 20200101 202111 00 601742.83 20200301 20211101
2345678912 ABC 20200101 202112 00 600840.19 20200301 20211201
2345678912 ABC 20200101 202201 00 599934.16 20200301 20220101
2345678912 ABC 20200101 202202 00 598924.73 20200301 20220201
2345678912 ABC 20200101 202203 00 597911.52 20200301 20220301
2345678912 ABC 20200101 202204 00 596994.51 20200301 20220401
2345678912 ABC 20200101 202205 00 596074.06 20200301 20220501
2345678912 ABC 20200101 202206 00 595050.16 20200301 20220601
2345678912 ABC 20200101 202207 00 593922.42 20200301 20220701
2345678912 ABC 20200101 202208 00 592790.45 20200301 20220801
2345678912 ABC 20200101 202209 00 591854.23 20200301 20220901
2345678912 ABC 20200101 202210 00 590914.50 20200301 20221001
2345678912 ABC 20200101 202211 00 589971.25 20200301 20221101
2345678912 ABC 20200101 202212 00 589024.46 20200301 20221201
2345678912 ABC 20200101 202301 00 588024.12 20200301 20230101
2345678912 ABC 20200101 202302 00 587070.03 20200301 20230201
2345678912 ABC 20200101 202303 00 585912.36 20200301 20230301
2345678912 ABC 20200101 202304 00 584950.35 20200301 20230401
2345678912 ABC 20200101 202305 00 583984.73 20200301 20230501
2345678912 ABC 20200101 202306 00 582915.49 20200301 20230601
2345678912 ABC 20200101 202307 00 581742.24 20200301 20230701
2345678912 ABC 20200101 202308 00 580464.59 20200301 20230801
2345678912 ABC 20200101 202309 00 579282.15 20200301 20230901
2345678912 ABC 20200101 202310 00 578045.28 20200301 20231001
2345678912 ABC 20200101 202311 00 576853.77 20200301 20231101
2345678912 ABC 20200101 202312 00 575857.79 20200301 20231201
2345678912 ABC 20200101 202401 00 574858.08 20200301 20240101
2345678912 ABC 20200101 202402 00 573854.62 20200301 20240201
3456789123 ABC 20210101 202101 00 347750.00 20210301 null
3456789123 ABC 20210101 202102 08 0.00 20210301 null
4567891234 ABC 20210101 202103 00 211760.38 20210301 20210401
4567891234 ABC 20210101 202104 00 211086.84 20210301 20210601
4567891234 ABC 20210101 202105 00 211424.10 20210301 20210501
4567891234 ABC 20210101 202106 00 211086.84 20210301 20210601
4567891234 ABC 20210101 202107 00 210748.60 20210301 20210701
4567891234 ABC 20210101 202108 01 210748.60 20210301 20210701
4567891234 ABC 20210101 202109 01 210409.37 20210301 20210801
4567891234 ABC 20210101 202110 02 210409.37 20210301 20210801
4567891234 ABC 20210101 202111 03 210409.37 20210301 20210801
4567891234 ABC 20210101 202112 04 210409.37 20210301 20210801
4567891234 ABC 20210101 202201 04 210409.37 20210301 20210801
4567891234 ABC 20210101 202202 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202203 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202204 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202205 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202206 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202207 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202208 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202209 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202210 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202211 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202212 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202301 00 223192.73 20210301 20230201
4567891234 ABC 20210101 202302 00 222830.66 20210301 20230301
4567891234 ABC 20210101 202303 00 222467.53 20210301 20230401
4567891234 ABC 20210101 202304 00 222467.53 20210301 20230401
4567891234 ABC 20210101 202305 00 222103.34 20210301 20230501
4567891234 ABC 20210101 202306 01 222103.34 20210301 20230501
4567891234 ABC 20210101 202307 01 221738.09 20210301 20230601
4567891234 ABC 20210101 202308 02 221738.09 20210301 20230601
4567891234 ABC 20210101 202309 03 221738.09 20210301 20230601
4567891234 ABC 20210101 202310 04 221738.09 20210301 20230601
4567891234 ABC 20210101 202311 04 221738.09 20210301 20230601
4567891234 ABC 20210101 202312 05 221738.09 20210301 20230601
4567891234 ABC 20210101 202401 05 221738.09 20210301 20230601
4567891234 ABC 20210101 202402 05 221738.09 20210301 20230601
5678912345 ABC 20220205 202203 00 232300.00 20220401 null
5678912345 ABC 20220205 202204 00 231886.32 20220401 20220401
5678912345 ABC 20220205 202205 00 231471.58 20220401 20220501
5678912345 ABC 20220205 202206 00 231055.77 20220401 20220601
5678912345 ABC 20220205 202207 00 230638.90 20220401 20220701
5678912345 ABC 20220205 202208 00 230220.95 20220401 20220801
5678912345 ABC 20220205 202209 00 229801.93 20220401 20220901
5678912345 ABC 20220205 202210 00 229381.83 20220401 20221001
5678912345 ABC 20220205 202211 00 228960.65 20220401 20221101
5678912345 ABC 20220205 202212 00 228538.39 20220401 20221201
5678912345 ABC 20220205 202301 00 228115.04 20220401 20230101
5678912345 ABC 20220205 202302 00 227690.61 20220401 20230201
5678912345 ABC 20220205 202303 00 227265.08 20220401 20230301
5678912345 ABC 20220205 202304 00 226838.46 20220401 20230401
5678912345 ABC 20220205 202305 00 226410.74 20220401 20230501
5678912345 ABC 20220205 202306 00 225981.92 20220401 20230601
5678912345 ABC 20220205 202307 00 225552.00 20220401 20230701
5678912345 ABC 20220205 202308 00 225120.98 20220401 20230801
5678912345 ABC 20220205 202309 08 0.00 20220401 20230901
6789123456 ABC 20220205 202203 00 792606.00 20220401 null
6789123456 ABC 20220205 202204 00 791385.64 20220401 20220401
6789123456 ABC 20220205 202205 00 788933.85 20220401 20220601
6789123456 ABC 20220205 202206 00 788933.85 20220401 20220601
6789123456 ABC 20220205 202207 00 787702.40 20220401 20220701
6789123456 ABC 20220205 202208 00 786467.23 20220401 20220801
6789123456 ABC 20220205 202209 00 785228.33 20220401 20220901
6789123456 ABC 20220205 202210 00 783985.68 20220401 20221001
6789123456 ABC 20220205 202211 00 782739.28 20220401 20221101
6789123456 ABC 20220205 202212 01 782739.28 20220401 20221101
6789123456 ABC 20220205 202301 00 780235.17 20220401 20230101
6789123456 ABC 20220205 202302 00 778977.44 20220401 20230201
6789123456 ABC 20220205 202303 00 777715.91 20220401 20230301
6789123456 ABC 20220205 202304 00 776450.57 20220401 20230401
6789123456 ABC 20220205 202305 00 775181.41 20220401 20230501
6789123456 ABC 20220205 202306 00 773908.41 20220401 20230601
6789123456 ABC 20220205 202307 00 772631.57 20220401 20230701
6789123456 ABC 20220205 202308 00 771350.87 20220401 20230801
6789123456 ABC 20220205 202309 00 770066.30 20220401 20230901
6789123456 ABC 20220205 202310 01 770066.30 20220401 20230901
6789123456 ABC 20220205 202311 02 770066.30 20220401 20230901
6789123456 ABC 20220205 202312 02 768777.85 20220401 20231001
6789123456 ABC 20220205 202401 02 767485.51 20220401 20231101
6789123456 ABC 20220205 202402 03 767485.51 20220401 20231101
;
run;

 

0 Likes
Reply
SASKiwi
PROC Star SASKiwi
PROC Star

Re: SAS EG Programming for Loan Performance

Posted 04-21-2024 06:26 PM (1792 views)  |   In reply to WorkUser

Have a look at this to see if it is close to your requirements. I've read all your dates into SAS dates so I can do date calculations.

data me1;
  input LOAN_NBR $10. SUBMITTING_ENTITY_ID $ ORIG_DTE :yymmdd8. MTHLY_ACTIVITY_DTE :yymmn6. LOAN_STATUS_CDE $ CUR_UPB_AMT :comma. FIRST_PMT_DTE :yymmdd8. LPI_DTE :yymmdd8.;
  format ORIG_DTE FIRST_PMT_DTE LPI_DTE MTHLY_ACTIVITY_DTE date9.;
  Orig_Year = year(ORIG_DTE);
  Year_of_Activity = year(MTHLY_ACTIVITY_DTE);
  datalines;
1234567890 ABC 20200101 202001 00 164780.00 20200301 .
1234567890 ABC 20200101 202002 00 164780.00 20200301 .
1234567890 ABC 20200101 202003 00 164581.64 20200301 20200301
1234567890 ABC 20200101 202004 00 164382.46 20200301 20200401
1234567890 ABC 20200101 202005 00 164182.45 20200301 20200501
1234567890 ABC 20200101 202006 00 163981.61 20200301 20200601
1234567890 ABC 20200101 202007 00 163779.93 20200301 20200701
1234567890 ABC 20200101 202008 00 163577.41 20200301 20200801
1234567890 ABC 20200101 202009 00 163374.05 20200301 20200901
1234567890 ABC 20200101 202010 00 163169.84 20200301 20201001
1234567890 ABC 20200101 202011 01 163169.84 20200301 20201001
1234567890 ABC 20200101 202012 01 162964.78 20200301 20201101
1234567890 ABC 20200101 202101 02 162964.78 20200301 20201101
1234567890 ABC 20200101 202102 03 162964.78 20200301 20201101
1234567890 ABC 20200101 202103 03 162758.87 20200301 20201201
1234567890 ABC 20200101 202104 04 162758.87 20200301 20201201
1234567890 ABC 20200101 202105 04 162552.11 20200301 20210101
1234567890 ABC 20200101 202106 04 162344.49 20200301 20210201
1234567890 ABC 20200101 202107 04 162136.00 20200301 20210301
1234567890 ABC 20200101 202108 04 161926.65 20200301 20210401
1234567890 ABC 20200101 202109 04 161716.42 20200301 20210501
1234567890 ABC 20200101 202110 00 165301.16 20200301 20211001
1234567890 ABC 20200101 202111 00 165192.56 20200301 20211101
1234567890 ABC 20200101 202112 00 165083.51 20200301 20211201
1234567890 ABC 20200101 202201 00 164974.00 20200301 20220101
1234567890 ABC 20200101 202202 01 164974.00 20200301 20220101
1234567890 ABC 20200101 202203 01 164864.04 20200301 20220201
1234567890 ABC 20200101 202204 01 164753.62 20200301 20220301
1234567890 ABC 20200101 202205 02 164753.62 20200301 20220301
1234567890 ABC 20200101 202206 03 164753.62 20200301 20220301
1234567890 ABC 20200101 202207 03 164642.74 20200301 20220401
1234567890 ABC 20200101 202208 04 164642.74 20200301 20220401
1234567890 ABC 20200101 202209 04 164642.74 20200301 20220401
1234567890 ABC 20200101 202210 05 164642.74 20200301 20220401
1234567890 ABC 20200101 202211 00 163738.95 20200301 20221201
1234567890 ABC 20200101 202212 00 163623.85 20200301 20230101
1234567890 ABC 20200101 202301 00 163508.27 20200301 20230201
1234567890 ABC 20200101 202302 24 0.00 20200301 20230201
2345678912 ABC 20200101 202001 00 623500.00 20200301 .
2345678912 ABC 20200101 202002 00 623500.00 20200301 .
2345678912 ABC 20200101 202003 00 622678.95 20200301 20200301
2345678912 ABC 20200101 202004 00 621854.82 20200301 20200401
2345678912 ABC 20200101 202005 00 621027.60 20200301 20200501
2345678912 ABC 20200101 202006 00 620197.27 20200301 20200601
2345678912 ABC 20200101 202007 00 619363.83 20200301 20200701
2345678912 ABC 20200101 202008 00 618527.26 20200301 20200801
2345678912 ABC 20200101 202009 00 617687.56 20200301 20200901
2345678912 ABC 20200101 202010 00 616844.71 20200301 20201001
2345678912 ABC 20200101 202011 00 615998.70 20200301 20201101
2345678912 ABC 20200101 202012 00 614297.15 20200301 20210101
2345678912 ABC 20200101 202101 00 613441.58 20200301 20210201
2345678912 ABC 20200101 202102 00 612582.81 20200301 20210301
2345678912 ABC 20200101 202103 00 611520.82 20200301 20210401
2345678912 ABC 20200101 202104 00 608933.74 20200301 20210601
2345678912 ABC 20200101 202105 00 608933.74 20200301 20210601
2345678912 ABC 20200101 202106 00 605476.47 20200301 20210801
2345678912 ABC 20200101 202107 00 603587.83 20200301 20210901
2345678912 ABC 20200101 202108 00 603587.83 20200301 20210901
2345678912 ABC 20200101 202109 00 603587.83 20200301 20210901
2345678912 ABC 20200101 202110 00 602642.10 20200301 20211001
2345678912 ABC 20200101 202111 00 601742.83 20200301 20211101
2345678912 ABC 20200101 202112 00 600840.19 20200301 20211201
2345678912 ABC 20200101 202201 00 599934.16 20200301 20220101
2345678912 ABC 20200101 202202 00 598924.73 20200301 20220201
2345678912 ABC 20200101 202203 00 597911.52 20200301 20220301
2345678912 ABC 20200101 202204 00 596994.51 20200301 20220401
2345678912 ABC 20200101 202205 00 596074.06 20200301 20220501
2345678912 ABC 20200101 202206 00 595050.16 20200301 20220601
2345678912 ABC 20200101 202207 00 593922.42 20200301 20220701
2345678912 ABC 20200101 202208 00 592790.45 20200301 20220801
2345678912 ABC 20200101 202209 00 591854.23 20200301 20220901
2345678912 ABC 20200101 202210 00 590914.50 20200301 20221001
2345678912 ABC 20200101 202211 00 589971.25 20200301 20221101
2345678912 ABC 20200101 202212 00 589024.46 20200301 20221201
2345678912 ABC 20200101 202301 00 588024.12 20200301 20230101
2345678912 ABC 20200101 202302 00 587070.03 20200301 20230201
2345678912 ABC 20200101 202303 00 585912.36 20200301 20230301
2345678912 ABC 20200101 202304 00 584950.35 20200301 20230401
2345678912 ABC 20200101 202305 00 583984.73 20200301 20230501
2345678912 ABC 20200101 202306 00 582915.49 20200301 20230601
2345678912 ABC 20200101 202307 00 581742.24 20200301 20230701
2345678912 ABC 20200101 202308 00 580464.59 20200301 20230801
2345678912 ABC 20200101 202309 00 579282.15 20200301 20230901
2345678912 ABC 20200101 202310 00 578045.28 20200301 20231001
2345678912 ABC 20200101 202311 00 576853.77 20200301 20231101
2345678912 ABC 20200101 202312 00 575857.79 20200301 20231201
2345678912 ABC 20200101 202401 00 574858.08 20200301 20240101
2345678912 ABC 20200101 202402 00 573854.62 20200301 20240201
3456789123 ABC 20210101 202101 00 347750.00 20210301 .
3456789123 ABC 20210101 202102 08 0.00 20210301 .
4567891234 ABC 20210101 202103 00 211760.38 20210301 20210401
4567891234 ABC 20210101 202104 00 211086.84 20210301 20210601
4567891234 ABC 20210101 202105 00 211424.10 20210301 20210501
4567891234 ABC 20210101 202106 00 211086.84 20210301 20210601
4567891234 ABC 20210101 202107 00 210748.60 20210301 20210701
4567891234 ABC 20210101 202108 01 210748.60 20210301 20210701
4567891234 ABC 20210101 202109 01 210409.37 20210301 20210801
4567891234 ABC 20210101 202110 02 210409.37 20210301 20210801
4567891234 ABC 20210101 202111 03 210409.37 20210301 20210801
4567891234 ABC 20210101 202112 04 210409.37 20210301 20210801
4567891234 ABC 20210101 202201 04 210409.37 20210301 20210801
4567891234 ABC 20210101 202202 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202203 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202204 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202205 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202206 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202207 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202208 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202209 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202210 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202211 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202212 05 210409.37 20210301 20210801
4567891234 ABC 20210101 202301 00 223192.73 20210301 20230201
4567891234 ABC 20210101 202302 00 222830.66 20210301 20230301
4567891234 ABC 20210101 202303 00 222467.53 20210301 20230401
4567891234 ABC 20210101 202304 00 222467.53 20210301 20230401
4567891234 ABC 20210101 202305 00 222103.34 20210301 20230501
4567891234 ABC 20210101 202306 01 222103.34 20210301 20230501
4567891234 ABC 20210101 202307 01 221738.09 20210301 20230601
4567891234 ABC 20210101 202308 02 221738.09 20210301 20230601
4567891234 ABC 20210101 202309 03 221738.09 20210301 20230601
4567891234 ABC 20210101 202310 04 221738.09 20210301 20230601
4567891234 ABC 20210101 202311 04 221738.09 20210301 20230601
4567891234 ABC 20210101 202312 05 221738.09 20210301 20230601
4567891234 ABC 20210101 202401 05 221738.09 20210301 20230601
4567891234 ABC 20210101 202402 05 221738.09 20210301 20230601
5678912345 ABC 20220205 202203 00 232300.00 20220401 .
5678912345 ABC 20220205 202204 00 231886.32 20220401 20220401
5678912345 ABC 20220205 202205 00 231471.58 20220401 20220501
5678912345 ABC 20220205 202206 00 231055.77 20220401 20220601
5678912345 ABC 20220205 202207 00 230638.90 20220401 20220701
5678912345 ABC 20220205 202208 00 230220.95 20220401 20220801
5678912345 ABC 20220205 202209 00 229801.93 20220401 20220901
5678912345 ABC 20220205 202210 00 229381.83 20220401 20221001
5678912345 ABC 20220205 202211 00 228960.65 20220401 20221101
5678912345 ABC 20220205 202212 00 228538.39 20220401 20221201
5678912345 ABC 20220205 202301 00 228115.04 20220401 20230101
5678912345 ABC 20220205 202302 00 227690.61 20220401 20230201
5678912345 ABC 20220205 202303 00 227265.08 20220401 20230301
5678912345 ABC 20220205 202304 00 226838.46 20220401 20230401
5678912345 ABC 20220205 202305 00 226410.74 20220401 20230501
5678912345 ABC 20220205 202306 00 225981.92 20220401 20230601
5678912345 ABC 20220205 202307 00 225552.00 20220401 20230701
5678912345 ABC 20220205 202308 00 225120.98 20220401 20230801
5678912345 ABC 20220205 202309 08 0.00 20220401 20230901
6789123456 ABC 20220205 202203 00 792606.00 20220401 .
6789123456 ABC 20220205 202204 00 791385.64 20220401 20220401
6789123456 ABC 20220205 202205 00 788933.85 20220401 20220601
6789123456 ABC 20220205 202206 00 788933.85 20220401 20220601
6789123456 ABC 20220205 202207 00 787702.40 20220401 20220701
6789123456 ABC 20220205 202208 00 786467.23 20220401 20220801
6789123456 ABC 20220205 202209 00 785228.33 20220401 20220901
6789123456 ABC 20220205 202210 00 783985.68 20220401 20221001
6789123456 ABC 20220205 202211 00 782739.28 20220401 20221101
6789123456 ABC 20220205 202212 01 782739.28 20220401 20221101
6789123456 ABC 20220205 202301 00 780235.17 20220401 20230101
6789123456 ABC 20220205 202302 00 778977.44 20220401 20230201
6789123456 ABC 20220205 202303 00 777715.91 20220401 20230301
6789123456 ABC 20220205 202304 00 776450.57 20220401 20230401
6789123456 ABC 20220205 202305 00 775181.41 20220401 20230501
6789123456 ABC 20220205 202306 00 773908.41 20220401 20230601
6789123456 ABC 20220205 202307 00 772631.57 20220401 20230701
6789123456 ABC 20220205 202308 00 771350.87 20220401 20230801
6789123456 ABC 20220205 202309 00 770066.30 20220401 20230901
6789123456 ABC 20220205 202310 01 770066.30 20220401 20230901
6789123456 ABC 20220205 202311 02 770066.30 20220401 20230901
6789123456 ABC 20220205 202312 02 768777.85 20220401 20231001
6789123456 ABC 20220205 202401 02 767485.51 20220401 20231101
6789123456 ABC 20220205 202402 03 767485.51 20220401 20231101
;
run;

proc sql;
  create table me1_Sum as
  select  loan_nbr
         ,Year_of_Activity
		 ,min(FIRST_PMT_DTE) as FIRST_PMT_DTE_Min format = date9.
		 ,min(case
		    when loan_status_cde in ('03', '04', '05', '06') then MTHLY_ACTIVITY_DTE
			else .
		  end) as Date_In_Default_Min format = date9.
		 ,intck('MONTH', calculated FIRST_PMT_DTE_Min, calculated Date_In_Default_Min, 'C') as Months_Since_First_Pmt
  from me1
  where loan_status_cde in ('03', '04', '05', '06')
  group by loan_nbr
           ,Year_of_Activity
  ;
quit;
0 Likes
Reply
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

Re: SAS EG Programming for Loan Performance

Posted 04-23-2024 08:48 AM (1731 views)  |   In reply to SASKiwi

@SASKiwi Thank you! I was able to pretty much do the same with my dataset but how do I translate that to show me ever delinquency rate broken down by the first 12 months of payment. Also my data set is much bigger than the sample data set so it took a while for it to run to output the my version of "ME1_SUM". 

0 Likes
Reply
SASKiwi
PROC Star SASKiwi
PROC Star

Re: SAS EG Programming for Loan Performance

Posted 04-23-2024 04:01 PM (1701 views)  |   In reply to WorkUser

@WorkUser - In that case can you please post what you would expect to see in your output table for the supplied test data to ensure I get it right.

0 Likes
Reply
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

Re: SAS EG Programming for Loan Performance

Posted 04-24-2024 08:05 AM (1670 views)  |   In reply to SASKiwi

@SASKiwi So I am looking to calculate delinquency rate for the fist year on a loan by origination year. So for the sample data below is the payment history for the first 12 months of payment. (0 means that it was current and 1 means that is was delinquent). 

 

2020                          
  1234567890 0 0 0 0 0 0 0 0 0 0 0 1
  2345678912 0 0 0 0 0 0 0 0 0 0 0 0
2021                          
  3456789123 Paid off before first payment
  4567891234 0 0 0 0 0 0 0 0 1 1 1 1
2022                          
  5678912345 0 0 0 0 0 0 0 0 0 0 0 0
  6789123456 0 0 0 0 0 0 0 0 0 0 0 0

 

(I don't need the above tables as output, it was just to explain what i am trying to accomplish) :

The output that I would like to generate is below which is the delinquency rate (delinquent loans/total active loans):  

 

E90 Rate
Orig Year First Year Payments
  1 2 3 4 5 6 7 8 9 10 11 12
2020 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.5
2021 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0 1.0 1.0
2022 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

 

Thank you for helping me with this. 

0 Likes
Reply
SASKiwi
PROC Star SASKiwi
PROC Star

Re: SAS EG Programming for Loan Performance

Posted 05-05-2024 04:11 PM (1480 views)  |   In reply to WorkUser

Sorry for the slow response. Here is my logic that gets close to what you want. Hopefully it will give you a few ideas to try:

proc sql;
  create table me1_Sum as
  select  loan_nbr
         ,Year_of_Activity
		 ,min(FIRST_PMT_DTE) as FIRST_PMT_DTE_Min format = date9.
		 ,min(case
		    when loan_status_cde in ('03', '04', '05', '06') then MTHLY_ACTIVITY_DTE
			else .
		  end) as Date_In_Default_Min format = date9.
		 ,intck('MONTH', calculated FIRST_PMT_DTE_Min, calculated Date_In_Default_Min, 'C') as Months_Since_First_Pmt
  from me1
  where loan_status_cde in ('03', '04', '05', '06')
  group by loan_nbr
           ,Year_of_Activity
  ;
quit;

proc report data = me1_Sum;
  column loan_nbr Year_of_Activity Months_Since_First_Pmt;
  define Year_of_Activity / group;
  define Months_Since_First_Pmt / across;
  define loan_nbr / group;
run;

SASKiwi_0-1714939852927.png

 

0 Likes
Reply
WorkUser
Obsidian | Level 7 WorkUser
Obsidian | Level 7

Re: SAS EG Programming for Loan Performance

Posted 05-13-2024 11:49 AM (1379 views)  |   In reply to SASKiwi

@SASKiwi thank you, you have been a tremendous help on this. really appreciate your time on this.  

0 Likes
Reply

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Watch SAS Enterprise Guide vs. SAS Studio on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • ‎04-09-2024 10:57 AM
  • 2115 views
  • 0 likes
  • 2 in conversation