Search code examples
sas

How to get latest -1 table name

I have a query that gives me latest table name in a library but now i also have sitation where i want the lastest-1 table name for eg

if latest date is BRANCH_AQB_INDIA_JUL2023_MAIN

if i do -1 i want

BRANCH_AQB_INDIA_JUN2023_MAIN

Here is the code i have used for getting latest table

proc sql outobs=1;
    select memname
    from dictionary.tables
    where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
    order by input(scan(memname, -2, '_'), monyy7.)
desc;
Solution

  • Add the condition where the date is < max date and it will always return the second-to-last value.

    proc sql outobs=1;
    select memname, input(scan(memname, -2, '_'), monyy7.) as date format=monyy7.
    from dictionary.tables
    where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
    having calculated date < max(calculated date)
    order by calculated date desc;
quit;