I have a dataset with many loans having 6 different statuses. There dataset has a chronological source date (Work Days). I have code that finds the first and last day for each Status but, I am unable to capture the correct detail (correct groupings) when the status moves backward chronologically (because this then creates multiple groupings for the same status).
Here is my desired / correct result:
Ref No | XX00405201 | F_Day | L_Day |
1 | LOCKED | 4/6/2016 | 5/10/2016 |
2 | APPRVD | 5/11/2016 | 5/19/2016 |
3 | OTC | 5/20/2016 | 5/20/2016 |
4 | CLOSED | 5/23/2016 | 5/28/2016 |
5 | READY | 5/31/2016 | 6/29/2016 |
6 | POOLED | 6/30/2016 | 6/30/2016 |
7 | CLOSED | 7/1/2016 | 8/1/2016 |
8 | READY | 8/2/2016 | 8/29/2016 |
9 | POOLED | 8/30/2016 | 8/30/2016 |
Here is the result of my code:
Ref No | XX00405201 | F_Day | L_Day |
1 | LOCKED | 4/6/2016 | 5/10/2016 |
2 | APPRVD | 5/11/2016 | 5/19/2016 |
3 | OTC | 5/20/2016 | 5/20/2016 |
4 | CLOSED | 5/23/2016 | 8/1/2016 |
5 | READY | 5/31/2016 | 8/29/2016 |
6 | POOLED | 6/30/2016 | 8/30/2016 |
Notice that I can only get 6 rows. I need 9, 1 extra grouping for each: CLOSED, READY,POOLED.
This is my code and it only allows me only 1 grouping / Sort Order: StatusNo
Data A_2300405201_GRP;
set A_2300405201;
by StatusNo;
If first.StatusNo then Do
F_Stat_Day=Source_Date_dte;
Output;
end;
If last.StatusNo then Do
L_Stat_Day=Source_Date_dte;
Output;
end;
format F_Stat_Day mmddyy8. L_Stat_Day mmddyy8.;
run;
1. How can I get the F_Stat_Day and L_Stat_Day each time that the Status changes throughout the chronology of the Loan, EVEN IF IT IS A REPEAT STATUS?
2. I also want to evaluate ALL Loans in the dataset in the same manner. The above code only evaluates 1 Loan.
Many Thanks
Can you upload the input dataset with two loans, displaying variable names and the order of the observations.
Other Loans in the Dataset would look the same with differing patterns of Statues.
Loan_Number | Source_Date_dte | StatusNo | Status |
XX00405201 | 4/6/2016 | 1 | LOCKED |
XX00405201 | 4/7/2016 | 1 | LOCKED |
XX00405201 | 4/8/2016 | 1 | LOCKED |
XX00405201 | 4/11/2016 | 1 | LOCKED |
XX00405201 | 4/12/2016 | 1 | LOCKED |
XX00405201 | 4/13/2016 | 1 | LOCKED |
XX00405201 | 4/14/2016 | 1 | LOCKED |
XX00405201 | 4/15/2016 | 1 | LOCKED |
XX00405201 | 4/18/2016 | 1 | LOCKED |
XX00405201 | 4/19/2016 | 1 | LOCKED |
XX00405201 | 4/20/2016 | 1 | LOCKED |
XX00405201 | 4/21/2016 | 1 | LOCKED |
XX00405201 | 4/22/2016 | 1 | LOCKED |
XX00405201 | 4/25/2016 | 1 | LOCKED |
XX00405201 | 4/26/2016 | 1 | LOCKED |
XX00405201 | 4/27/2016 | 1 | LOCKED |
XX00405201 | 4/28/2016 | 1 | LOCKED |
XX00405201 | 4/29/2016 | 1 | LOCKED |
XX00405201 | 5/2/2016 | 1 | LOCKED |
XX00405201 | 5/3/2016 | 1 | LOCKED |
XX00405201 | 5/4/2016 | 1 | LOCKED |
XX00405201 | 5/5/2016 | 1 | LOCKED |
XX00405201 | 5/6/2016 | 1 | LOCKED |
XX00405201 | 5/9/2016 | 1 | LOCKED |
XX00405201 | 5/10/2016 | 1 | LOCKED |
XX00405201 | 5/11/2016 | 2 | APPRVD |
XX00405201 | 5/12/2016 | 2 | APPRVD |
XX00405201 | 5/13/2016 | 2 | APPRVD |
XX00405201 | 5/16/2016 | 2 | APPRVD |
XX00405201 | 5/17/2016 | 2 | APPRVD |
XX00405201 | 5/18/2016 | 2 | APPRVD |
XX00405201 | 5/19/2016 | 2 | APPRVD |
XX00405201 | 5/20/2016 | 3 | OTC |
XX00405201 | 5/23/2016 | 4 | CLOSED |
XX00405201 | 5/24/2016 | 4 | CLOSED |
XX00405201 | 5/25/2016 | 4 | CLOSED |
XX00405201 | 5/26/2016 | 4 | CLOSED |
XX00405201 | 5/27/2016 | 4 | CLOSED |
XX00405201 | 5/28/2016 | 4 | CLOSED |
XX00405201 | 5/31/2016 | 5 | READY |
XX00405201 | 6/2/2016 | 5 | READY |
XX00405201 | 6/3/2016 | 5 | READY |
XX00405201 | 6/6/2016 | 5 | READY |
XX00405201 | 6/7/2016 | 5 | READY |
XX00405201 | 6/8/2016 | 5 | READY |
XX00405201 | 6/9/2016 | 5 | READY |
XX00405201 | 6/10/2016 | 5 | READY |
XX00405201 | 6/13/2016 | 5 | READY |
XX00405201 | 6/14/2016 | 5 | READY |
XX00405201 | 6/15/2016 | 5 | READY |
XX00405201 | 6/16/2016 | 5 | READY |
XX00405201 | 6/17/2016 | 5 | READY |
XX00405201 | 6/20/2016 | 5 | READY |
XX00405201 | 6/21/2016 | 5 | READY |
XX00405201 | 6/22/2016 | 5 | READY |
XX00405201 | 6/23/2016 | 5 | READY |
XX00405201 | 6/24/2016 | 5 | READY |
XX00405201 | 6/27/2016 | 5 | READY |
XX00405201 | 6/28/2016 | 5 | READY |
XX00405201 | 6/29/2016 | 5 | READY |
XX00405201 | 6/30/2016 | 6 | POOLED |
XX00405201 | 7/1/2016 | 4 | CLOSED |
XX00405201 | 7/2/2016 | 4 | CLOSED |
XX00405201 | 7/6/2016 | 4 | CLOSED |
XX00405201 | 7/7/2016 | 4 | CLOSED |
XX00405201 | 7/8/2016 | 4 | CLOSED |
XX00405201 | 7/11/2016 | 4 | CLOSED |
XX00405201 | 7/12/2016 | 4 | CLOSED |
XX00405201 | 7/13/2016 | 4 | CLOSED |
XX00405201 | 7/14/2016 | 4 | CLOSED |
XX00405201 | 7/15/2016 | 4 | CLOSED |
XX00405201 | 7/18/2016 | 4 | CLOSED |
XX00405201 | 7/19/2016 | 4 | CLOSED |
XX00405201 | 7/20/2016 | 4 | CLOSED |
XX00405201 | 7/21/2016 | 4 | CLOSED |
XX00405201 | 7/22/2016 | 4 | CLOSED |
XX00405201 | 7/25/2016 | 4 | CLOSED |
XX00405201 | 7/26/2016 | 4 | CLOSED |
XX00405201 | 7/27/2016 | 4 | CLOSED |
XX00405201 | 7/28/2016 | 4 | CLOSED |
XX00405201 | 7/29/2016 | 4 | CLOSED |
XX00405201 | 8/1/2016 | 4 | CLOSED |
XX00405201 | 8/2/2016 | 5 | READY |
XX00405201 | 8/3/2016 | 5 | READY |
XX00405201 | 8/4/2016 | 5 | READY |
XX00405201 | 8/5/2016 | 5 | READY |
XX00405201 | 8/8/2016 | 5 | READY |
XX00405201 | 8/9/2016 | 5 | READY |
XX00405201 | 8/10/2016 | 5 | READY |
XX00405201 | 8/11/2016 | 5 | READY |
XX00405201 | 8/12/2016 | 5 | READY |
XX00405201 | 8/15/2016 | 5 | READY |
XX00405201 | 8/16/2016 | 5 | READY |
XX00405201 | 8/17/2016 | 5 | READY |
XX00405201 | 8/18/2016 | 5 | READY |
XX00405201 | 8/19/2016 | 5 | READY |
XX00405201 | 8/22/2016 | 5 | READY |
XX00405201 | 8/23/2016 | 5 | READY |
XX00405201 | 8/24/2016 | 5 | READY |
XX00405201 | 8/25/2016 | 5 | READY |
XX00405201 | 8/26/2016 | 5 | READY |
XX00405201 | 8/27/2016 | 5 | READY |
XX00405201 | 8/29/2016 | 5 | READY |
XX00405201 | 8/30/2016 | 6 | POOLED |
I assume the dataset is sorted by: lOAN_NUMBAER and SOURCE_DATE_DTE inside the loan.
Here is your code with small changes emphasized by underline:
Data A_2300405201_GRP;
set A_2300405201;
by loan_number source_date_dte StatusNo NOTSORTED;
retain F_Stat_Day;
If first.StatusNo then Do
F_Stat_Day=Source_Date_dte;
/*Output; */
end;
If last.StatusNo then Do
L_Stat_Day=Source_Date_dte;
Output;
end;
format F_Stat_Day mmddyy8. L_Stat_Day mmddyy8.;
run;
NOTES: 1) I havn't run the code.
2) Did you changed F_stat_day to F_Day ? L_Stat_day to L_Day ?
or did you added labels to display on output ?
(Your result doesn't fit your code variable names).
If I understand what you mean. data have; infile cards expandtabs truncover; input Loan_Number : $20. Source_Date_dte : mmddyy10. StatusNo Status :$20.; format Source_date_dte mmddyy10.; cards; XX00405201 4/6/2016 1 LOCKED XX00405201 4/7/2016 1 LOCKED XX00405201 4/8/2016 1 LOCKED XX00405201 4/11/2016 1 LOCKED XX00405201 4/12/2016 1 LOCKED XX00405201 4/13/2016 1 LOCKED XX00405201 4/14/2016 1 LOCKED XX00405201 4/15/2016 1 LOCKED XX00405201 4/18/2016 1 LOCKED XX00405201 4/19/2016 1 LOCKED XX00405201 4/20/2016 1 LOCKED XX00405201 4/21/2016 1 LOCKED XX00405201 4/22/2016 1 LOCKED XX00405201 4/25/2016 1 LOCKED XX00405201 4/26/2016 1 LOCKED XX00405201 4/27/2016 1 LOCKED XX00405201 4/28/2016 1 LOCKED XX00405201 4/29/2016 1 LOCKED XX00405201 5/2/2016 1 LOCKED XX00405201 5/3/2016 1 LOCKED XX00405201 5/4/2016 1 LOCKED XX00405201 5/5/2016 1 LOCKED XX00405201 5/6/2016 1 LOCKED XX00405201 5/9/2016 1 LOCKED XX00405201 5/10/2016 1 LOCKED XX00405201 5/11/2016 2 APPRVD XX00405201 5/12/2016 2 APPRVD XX00405201 5/13/2016 2 APPRVD XX00405201 5/16/2016 2 APPRVD XX00405201 5/17/2016 2 APPRVD XX00405201 5/18/2016 2 APPRVD XX00405201 5/19/2016 2 APPRVD XX00405201 5/20/2016 3 OTC XX00405201 5/23/2016 4 CLOSED XX00405201 5/24/2016 4 CLOSED XX00405201 5/25/2016 4 CLOSED XX00405201 5/26/2016 4 CLOSED XX00405201 5/27/2016 4 CLOSED XX00405201 5/28/2016 4 CLOSED XX00405201 5/31/2016 5 READY XX00405201 6/2/2016 5 READY XX00405201 6/3/2016 5 READY XX00405201 6/6/2016 5 READY XX00405201 6/7/2016 5 READY XX00405201 6/8/2016 5 READY XX00405201 6/9/2016 5 READY XX00405201 6/10/2016 5 READY XX00405201 6/13/2016 5 READY XX00405201 6/14/2016 5 READY XX00405201 6/15/2016 5 READY XX00405201 6/16/2016 5 READY XX00405201 6/17/2016 5 READY XX00405201 6/20/2016 5 READY XX00405201 6/21/2016 5 READY XX00405201 6/22/2016 5 READY XX00405201 6/23/2016 5 READY XX00405201 6/24/2016 5 READY XX00405201 6/27/2016 5 READY XX00405201 6/28/2016 5 READY XX00405201 6/29/2016 5 READY XX00405201 6/30/2016 6 POOLED XX00405201 7/1/2016 4 CLOSED XX00405201 7/2/2016 4 CLOSED XX00405201 7/6/2016 4 CLOSED XX00405201 7/7/2016 4 CLOSED XX00405201 7/8/2016 4 CLOSED XX00405201 7/11/2016 4 CLOSED XX00405201 7/12/2016 4 CLOSED XX00405201 7/13/2016 4 CLOSED XX00405201 7/14/2016 4 CLOSED XX00405201 7/15/2016 4 CLOSED XX00405201 7/18/2016 4 CLOSED XX00405201 7/19/2016 4 CLOSED XX00405201 7/20/2016 4 CLOSED XX00405201 7/21/2016 4 CLOSED XX00405201 7/22/2016 4 CLOSED XX00405201 7/25/2016 4 CLOSED XX00405201 7/26/2016 4 CLOSED XX00405201 7/27/2016 4 CLOSED XX00405201 7/28/2016 4 CLOSED XX00405201 7/29/2016 4 CLOSED XX00405201 8/1/2016 4 CLOSED XX00405201 8/2/2016 5 READY XX00405201 8/3/2016 5 READY XX00405201 8/4/2016 5 READY XX00405201 8/5/2016 5 READY XX00405201 8/8/2016 5 READY XX00405201 8/9/2016 5 READY XX00405201 8/10/2016 5 READY XX00405201 8/11/2016 5 READY XX00405201 8/12/2016 5 READY XX00405201 8/15/2016 5 READY XX00405201 8/16/2016 5 READY XX00405201 8/17/2016 5 READY XX00405201 8/18/2016 5 READY XX00405201 8/19/2016 5 READY XX00405201 8/22/2016 5 READY XX00405201 8/23/2016 5 READY XX00405201 8/24/2016 5 READY XX00405201 8/25/2016 5 READY XX00405201 8/26/2016 5 READY XX00405201 8/27/2016 5 READY XX00405201 8/29/2016 5 READY XX00405201 8/30/2016 6 POOLED ; run; data want; set have; by Status notsorted; retain f_date ; if first.Status then f_date=Source_Date_dte; if last.Status then do;l_date=Source_Date_dte;output;end; format f_date l_date mmddyy10.; keep status f_date l_date; run;
Do you want to calculate the F_DATE L_DATE per each loan ? or per the whole file ?
As mutch as I understand your input contains many loans.
I suggest to run the two versions:
1) using BY sttaemant as I wrote:
BY loan_number source_date_dte StatusNo NOTSORTED;
2) using BY StatusNo notsorted only
Compare the two outputs and define what fits your expectations better.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.