I have two columns:
Data is about the transactions made by different accounts. So, GRP_ACCT_NO has recurring values and the TRANS_DATE gives the date, that particular account made a transaction on. I have about 1.5 million records in the data set but only 97k unique accounts. I want to find the second latest transaction date for each Account in SAS
Here is the code which I tried:
create table second_latest_trans as
select GRP_ACCT_NO,Max(TRANS_DATE) from project.spend as ps
where TRANS_DATE < (select max(TRANS_DATE)
from project.spend as ps2
where ps.GRP_ACCT_NO = ps2.GRP_ACCT_NO
group by GRP_ACCT_NO)
group by GRP_ACCT_NO;
SQL has no concept of order of observations. Use a DATA step. If your data is not already sorted then sort it (create an index).
If you just want the second record, even if there are ties then you can just count the records for each account.
data second_latest_trans; set project.spend; by GRP_ACCT_NO TRANS_DATE; if first.grp_acct then recno=0; recno+1; if recno=2 then output; run;
If there are multiple records for the same value of TRANS_DATE and you want the second distinct value of TRANS_DATE then this more complicated step would work.
data second_latest_trans; set project.spend; by GRP_ACCT_NO TRANS_DATE; if first.grp_acct then found=0; else if not found and first.trans_date then do; output; found=1; end; retain found; run;