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;
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;
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;
Thanks Kurt .. this works for me !!
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.