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 !!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.