calculating second last date in a table using left join

[farrukh]

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;

[Kurt_Bremser · Accepted solution]

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;

[farrukh]

Thanks Kurt .. this works for me !!