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
... View more