topic Re: Find First and Last Dates in a dataset with repeating groups in SAS Programming

Find First and Last Dates in a dataset with repeating groups

Kody_devl — Wed, 31 Aug 2016 13:22:20 GMT

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

Re: Find First and Last Dates in a dataset with repeating groups

Shmuel — Wed, 31 Aug 2016 13:57:12 GMT

Can you upload the input dataset with two loans, displaying variable names and the order of the observations.

Re: Find First and Last Dates in a dataset with repeating groups

Kody_devl — Wed, 31 Aug 2016 14:06:29 GMT

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

Re: Find First and Last Dates in a dataset with repeating groups

Shmuel — Wed, 31 Aug 2016 14:40:08 GMT

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).

Re: Find First and Last Dates in a dataset with repeating groups

Ksharp — Thu, 01 Sep 2016 07:50:23 GMT

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;

Re: Find First and Last Dates in a dataset with repeating groups

Shmuel — Thu, 01 Sep 2016 09:32:49 GMT

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.