user3252148 user3252148 - 5 months ago 32
SQL Question

Finding the second latest date in SAS

I have two columns:

GRP_ACCT_NO, TRANS_DATE

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:

proc sql;
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;
quit;


The code doesn't seem to get any results. Its taking a long time to load.

Please help!!

Tom Tom
Answer

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;
Comments