topic How to get the oldest data from multiple dates across columns in New SAS User

How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 15:38:53 GMT

Hi,

I am trying to get the oldest date across multiple dates and flag each according to the column title.

below is the example data:P

CUSTOMER_KEY	SERVICE_KEY	First_date	Second_date	Third_date	Fourth_date
123456	654321	.	.	8/14/2012	.
123457	6543212	.	.	1/8/2013	.
123458	12432103	.	6/29/2010	.	.
123459	18320994	.	.	12/15/2017	.
123460	24209885	.	.	2/19/2018	.
123461	30098776	12/19/2011	3/23/2010	7/3/2012	.
123462	35987667	10/7/2012	4/19/2010	7/12/2012	.
123463	41876558	10/7/2012	12/1/2010	7/28/2012	.
123464	47765449	10/7/2012	10/27/2011	6/7/2013	.
123465	53654340	10/7/2012	11/28/2011	8/13/2013	.
123466	59543231	10/7/2012	11/28/2011	6/17/2014	.
123467	65432122	10/7/2012	11/28/2011	6/24/2014	.
123468	71321013	10/7/2012	11/28/2011	6/30/2014	.
123469	77209904	10/7/2012	11/28/2011	11/14/2014	.
123470	83098795	10/7/2012	11/28/2011	11/19/2014	.
123471	88987686	10/7/2012	11/28/2011	11/21/2014	.

This is the code I was trying to use in proc sql

Thank you all for your help!

case

when first_date < second_date and first_date < third_date and first_date < fourth_date then Service1

when second_date < first_date and second_date < third_date and second_date < fourth_date then Service2

when third_date < first_date and third_date < second_date and third_date < fourth_date then Service3

when fourth_date < first_date and fourth_date < second_date and fourth_date < third_date then Service1

else NonService

end as FirstTrans

Re: How to get the oldest data from multiple dates across columns

novinosrin — Thu, 29 Nov 2018 15:41:05 GMT

use min/max

Re: How to get the oldest data from multiple dates across columns

novinosrin — Thu, 29 Nov 2018 15:45:19 GMT

data have;
infile cards truncover;
input CUSTOMER_KEY	SERVICE_KEY	(First_date	Second_date	Third_date	Fourth_date) (:mmddyy10.);
format First_date--Fourth_date mmddyy10.;
cards;
123456	654321	.	.	8/14/2012	.
123457	6543212	.	.	1/8/2013	.
123458	12432103	.	6/29/2010	.	.
123459	18320994	.	.	12/15/2017	.
123460	24209885	.	.	2/19/2018	.
123461	30098776	12/19/2011	3/23/2010	7/3/2012	.
123462	35987667	10/7/2012	4/19/2010	7/12/2012	.
123463	41876558	10/7/2012	12/1/2010	7/28/2012	.
123464	47765449	10/7/2012	10/27/2011	6/7/2013	.
123465	53654340	10/7/2012	11/28/2011	8/13/2013	.
123466	59543231	10/7/2012	11/28/2011	6/17/2014	.
123467	65432122	10/7/2012	11/28/2011	6/24/2014	.
123468	71321013	10/7/2012	11/28/2011	6/30/2014	.
123469	77209904	10/7/2012	11/28/2011	11/14/2014	.
123470	83098795	10/7/2012	11/28/2011	11/19/2014	.
123471	88987686	10/7/2012	11/28/2011	11/21/2014	.
;

data want;
set have;
want=min(of first_date--fourth_date);
format want mmddyy10.;
run;

Re: How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 15:49:11 GMT

Hi Novinosrin,

Thank you for the suggestion, what I was trying to do is to name each statement according to what the date is for.

If first_date is the most recent among all the dates then i want to name it as service 1,

if second_date is the most recent among all the dates then i want to name it as service 2,

and so on,

Re: How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 15:50:45 GMT

....and add a column to reflect the name

Re: How to get the oldest data from multiple dates across columns

novinosrin — Thu, 29 Nov 2018 15:59:27 GMT

It would be better if you post a sample of your required output

Re: How to get the oldest data from multiple dates across columns

Reeza — Thu, 29 Nov 2018 16:30:32 GMT

Use MIN(), WHICHN()

data want;
set have;

min_date = min(of first_date, second_date, ...);
location_min_date = whichn(x, of first_date, second_date, ...);

run;

What about duplicates though? This will capture only the first value.

If this becomes more of an issue, I would recommend sorting the data and holding two arrays, one with date, one with order. Or flip your data to long. That makes this very easy.

@Eugenio21 wrote:

Hi,

I am trying to get the oldest date across multiple dates and flag each according to the column title.

below is the example data:P

CUSTOMER_KEY SERVICE_KEY First_date Second_date Third_date Fourth_date

123456 654321 . . 8/14/2012 .

123457 6543212 . . 1/8/2013 .

123458 12432103 . 6/29/2010 . .

123459 18320994 . . 12/15/2017 .

123460 24209885 . . 2/19/2018 .

123461 30098776 12/19/2011 3/23/2010 7/3/2012 .

123462 35987667 10/7/2012 4/19/2010 7/12/2012 .

123463 41876558 10/7/2012 12/1/2010 7/28/2012 .

123464 47765449 10/7/2012 10/27/2011 6/7/2013 .

123465 53654340 10/7/2012 11/28/2011 8/13/2013 .

123466 59543231 10/7/2012 11/28/2011 6/17/2014 .

123467 65432122 10/7/2012 11/28/2011 6/24/2014 .

123468 71321013 10/7/2012 11/28/2011 6/30/2014 .

123469 77209904 10/7/2012 11/28/2011 11/14/2014 .

123470 83098795 10/7/2012 11/28/2011 11/19/2014 .

123471 88987686 10/7/2012 11/28/2011 11/21/2014 .

This is the code I was trying to use in proc sql

Thank you all for your help!

case

when first_date < second_date and first_date < third_date and first_date < fourth_date then Service1

when second_date < first_date and second_date < third_date and second_date < fourth_date then Service2

when third_date < first_date and third_date < second_date and third_date < fourth_date then Service3

when fourth_date < first_date and fourth_date < second_date and fourth_date < third_date then Service1

else NonService

end as FirstTrans

Re: How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 17:00:43 GMT

Thank you Reeza. ALl the data is unique so there is not duplicates. I ran your code and it did not give me the result i was looking for.

below is the code that I recently use however the nulls are not recognized in the calculation

,case
when first_loan_date < (first_chq_date and first_chq_date is not missing) or first_loan_date < (first_service_date and first_service_date is not missing) or first_loan_date < (first_ex_date and first_ex_date is not missing) then 'FirstTransClt'
ELSE 'None' end as First_Trans

Re: How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 17:02:11 GMT

...what I am trying to do is to get the oldest date from the four columns of dates and name it as first trans

Re: How to get the oldest data from multiple dates across columns

Reeza — Thu, 29 Nov 2018 17:09:31 GMT

It should have worked. post the exact code you used and the log please. Also, explain how it does not 'work'. It should have given you 1, 2, 3, etc for the location_min_date and you can then convert that to 'first_trans' or whatever text you want. If it's the variable name you can also use an ARRAY method but that's manual and more work.

Untested:

data want;
set have;
array dates(*) <list your dates variables>;

Min_date = min(of dates(*));
do i=1 to dim(dates);
if dates(i) = min_date then do;
found = vname (dates(i));
leave;
end;
end;

run;

Re: How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 19:08:57 GMT

hello!

this is the example data I have:

CUSTOMER_KEY	SERVICE_KEY	First_Loan_date	First_Chq_date	First_Service_date	First_Ex_date
800001	70001	9/16/2012	10/6/2011	11/1/2012	.
800002	70002	10/18/2012	10/13/2011	11/15/2012	.
800003	70003	11/1/2012	10/22/2011	12/21/2012	.
800004	70004	11/15/2012	8/27/2012	1/27/2013	.
800005	70005	11/29/2012	11/1/2012	1/24/2014	.
800006	70006	12/13/2012	11/15/2012	6/30/2015	.
800007	70007	1/10/2013	11/29/2012	8/3/2015	.
800008	70008	1/27/2013	12/13/2012	9/30/2015	.
800009	70009	1/27/2013	12/21/2012	1/13/2016	.
800010	70010	1/27/2013	1/10/2013	4/10/2016	.
800011	70011	1/27/2013	1/10/2013	7/28/2016	.
800012	70012	1/27/2013	1/10/2013	11/27/2017	.
800013	70013	1/27/2013	1/10/2013	12/9/2017	.
800014	70014	1/27/2013	1/10/2013	12/14/2017	.
800015	70015	1/27/2013	1/10/2013	12/20/2017	.
800016	70016	1/27/2013	1/10/2013	3/22/2018	.
800017	70017	1/27/2013	1/10/2013	3/22/2018	.
800018	70018	10/6/2011	1/9/2015	.	.
800019	70019	10/26/2011	1/9/2015	.	.
800020	70020	11/10/2011	1/9/2015	.	.
800021	70021	11/18/2011	1/9/2015	.	.
800022	70022	11/25/2011	1/9/2015	.	.
800023	70023	10/6/2011	.	.	.
800024	70024	5/12/2012	10/6/2011	.	.
800025	70025	6/6/2012	2/2/2012	.	.
800026	70026	10/6/2011	.	4/27/2014	2/8/2015
800027	70027	10/28/2011	.	8/7/2014	3/18/2015
800028	70028	10/31/2012	.	4/15/2015	8/1/2016
800029	70029	1/3/2013	.	5/21/2015	8/1/2016
800030	70030	4/30/2013	.	8/1/2016	8/1/2016

and from that data, I want to produce this table below:

CUSTOMER_KEY	SERVICE_KEY	First_Service	First_Loan_date	First_Chq_date	First_Service_date	First_Ex_date
800001	70001	Chq	9/16/2012	10/6/2011	11/1/2012	.
800002	70002	Chq	10/18/2012	10/13/2011	11/15/2012	.
800003	70003	Chq	11/1/2012	10/22/2011	12/21/2012	.
800004	70004	Chq	11/15/2012	8/27/2012	1/27/2013	.
800005	70005	Chq	11/29/2012	11/1/2012	1/24/2014	.
800006	70006	Chq	12/13/2012	11/15/2012	6/30/2015	.
800007	70007	Chq	1/10/2013	11/29/2012	8/3/2015	.
800008	70008	Loan	1/27/2012	12/13/2012	9/30/2015	.
800009	70009	Loan	1/27/2012	12/21/2012	1/13/2016	.
800010	70010	Loan	1/27/2012	1/10/2013	4/10/2016	.
800011	70011	Loan	1/27/2012	1/10/2013	7/28/2016	.
800012	70012	Loan	1/27/2012	1/10/2013	11/27/2017	.
800013	70013	Loan	1/27/2012	1/10/2013	12/9/2017	.
800014	70014	Loan	1/27/2012	1/10/2013	12/14/2017	.
800015	70015	Loan	1/27/2012	1/10/2013	12/20/2017	.
800016	70016	Loan	1/27/2012	1/10/2013	3/22/2018	.
800017	70017	Loan	1/27/2012	1/10/2013	3/22/2018	.
800018	70018	Loan	10/6/2011	1/9/2015	.	.
800019	70019	Loan	10/26/2011	1/9/2015	.	.
800020	70020	Loan	11/10/2011	1/9/2015	.	.
800021	70021	Loan	11/18/2011	1/9/2015	.	.
800022	70022	Loan	11/25/2011	1/9/2015	.	.
800023	70023	Loan	10/6/2011	.	.	.
800024	70024	Chq	5/12/2012	10/6/2011	.	.
800025	70025	Chq	6/6/2012	2/2/2012	.	.
800026	70026	Loan	10/6/2011	.	4/27/2014	2/8/2015
800027	70027	Loan	10/28/2011	.	8/7/2014	3/18/2015
800028	70028	Loan	10/31/2012	.	4/15/2015	8/1/2016
800029	70029	Loan	1/3/2013	.	5/21/2015	8/1/2016
800030	70030	Loan	4/30/2013	.	8/1/2016	8/1/2016

...the column 'First_Trans' refers to the oldest date in that particular row.

I have used the below case statement in proc sql, however did not give me what I wanted.

,case
when lr.first_loan_date < (cr.first_chq_date and sr.first_service_date and er.first_ex_date) then 'Loan'
when cr.first_chq_date < (lr.first_loan_date and sr.first_service_date and er.first_ex_date) then 'Chq'
when sr.first_service_date < (lr.first_loan_date and cr.first_chq_date and er.first_ex_date) then 'Service'
when er.first_ex_date < (lr.first_loan_date and sr.first_service_date and cr.first_chq_date) then 'Currency'
ELSE 'None' end as First_Trans

the code above is not generating what I wanted to, below is the result I get:

CUSTOMER_KEY	First_Trans	First_Loan	First_Chq_Cashed	First_Service	First_Currency_Exchange
80000001	Service	10/31/2008	2/4/2010	.	.
80000002	Chq	10/31/2008	.	.	.
80000003	Currency	10/31/2008	1/2/2015	3/30/2012	.
80000004	Currency	10/31/2008	3/10/2010	4/30/2012	.
80000005	Chq	10/31/2008	.	.	.
80000006	Currency	10/31/2008	6/12/2014	4/15/2012	.
80000007	Chq	10/31/2008	.	.	.
80000008	Chq	11/1/2008	.	.	.
80000009	Chq	11/1/2008	.	.	.
80000010	Service	11/1/2008	11/19/2008	.	.
80000011	Chq	11/1/2008	.	.	.
80000012	Chq	11/1/2008	.	8/12/2016	.
80000013	Chq	11/1/2008	.	11/23/2012	.
80000014	Chq	11/1/2008	.	.	.
80000015	Chq	11/1/2008	.	9/12/2013	.
80000016	Chq	11/1/2008	.	6/19/2012	.
80000017	Chq	11/1/2008	.	.	.
80000018	Service	11/1/2008	11/11/2008	.	.

in the first row, it says First_Tran is 'Service', however the First_Service data is blank or null and all rows are like that. The code is not identifying whether the referred date is lover than the rest of the dates in the same row.

Thanks for helping.

Re: How to get the oldest data from multiple dates across columns

Eugenio21 — Thu, 29 Nov 2018 19:10:23 GMT

correction...'lower' than the rest of the dates in the same row.

Re: How to get the oldest data from multiple dates across columns

Reeza — Thu, 29 Nov 2018 22:05:30 GMT

If it’s SQL just keep checking your conditions until you get what’s you want. I wouldn’t do an SQL solution for this type of problem. Your brackets are in the wrong places for your conditions though. And do one at a time so you understand it.