DATA Step, Macro, Functions and more

calculating second last date in a table using left join

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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: 7,833

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

View solution in original post


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

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
Occasional Contributor
Posts: 8

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

Posted in reply to KurtBremser

Thanks Kurt .. this works for me !!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 221 views
  • 1 like
  • 2 in conversation