## calculating second last date in a table using left join

Solved
Occasional Contributor
Posts: 11

# calculating second last date in a table using left join

data test;
input cust_id trans_date ddmmyy10.;
cards;
1 25/08/2016
1 02/09/2016
1 28/08/2016
1 27/08/2016
1 26/08/2016
2 02/09/2016
2 29/09/2016
2 28/08/2016
2 01/09/2016
;
run;

/*I want to get a column name as sec_date for the above table per customer id, i had used the below codes but it is not working, please help*/

proc sql;
create table sample as
select cust_id
,min(trans_date) as sec_date
from test A
LEFT OUTER JOIN
(
select cust_id
,min(trans_date) as min_date
from test ) as src
ON A.cust_id=src.cust_id and A.sec_date > src.min_date
group by cust_id
;quit;

Accepted Solutions
Solution
‎09-19-2016 02:33 AM
Super User
Posts: 10,280

## Re: calculating second last date in a table using left join

[ Edited ]

If you want to get the second-to-last date for each customer, just reverse the order of the dates and select the second observation for each by group:

proc sort data=test;
by cust_id descending trans_date;
run;

data sample (keep=cust_id trans_date rename=(trans_date=sec_date));
set test;
by cust_id;
if first.cust_id
then counter = 1;
else counter + 1;
if counter = 2;
run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎09-19-2016 02:33 AM
Super User
Posts: 10,280

## Re: calculating second last date in a table using left join

[ Edited ]

If you want to get the second-to-last date for each customer, just reverse the order of the dates and select the second observation for each by group:

proc sort data=test;
by cust_id descending trans_date;
run;

data sample (keep=cust_id trans_date rename=(trans_date=sec_date));
set test;
by cust_id;
if first.cust_id
then counter = 1;
else counter + 1;
if counter = 2;
run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11