How can I retrieve a list of table names and arrange them in ascending order based on their dates?

Question

I have some rows like this

BRANCH_AQB_INDIA_NOV2022_MAIN
BRANCH_AQB_INDIA_FEB2023_MAIN
BRANCH_AQB_INDIA_SEP2023_MAIN
BRANCH_AQB_INDIA_AUG2022_MAIN
BRANCH_AQB_INDIA_APR2023_MAIN
BRANCH_AQB_INDIA_JAN2024_MAIN

i want to sort them by asc like this

BRANCH_AQB_INDIA_AUG2022_MAIN
BRANCH_AQB_INDIA_NOV2022_MAIN
BRANCH_AQB_INDIA_FEB2023_MAIN
BRANCH_AQB_INDIA_APR2023_MAIN
BRANCH_AQB_INDIA_SEP2023_MAIN
BRANCH_AQB_INDIA_JAN2024_MAIN

Currently I'm just getting all the list with asc order like this in SAS

`
proc sql;
    select memname
    from dictionary.tables
    where libname = 'KALP51' and memname like 'BRANCH_AQB_INDIA_%_MAIN'
    order by memname asc;
quit;`

Answer 1

Extract the date string from memname, convert it to a SAS date, and order by that value. You can do this with scan to get the second-to-last date string in MONYY7. format, then use input to convert it to a SAS date. Use the outobs=1 option to only get the first row in the list.

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.);
quit;`

memname
BRANCH_AQB_INDIA_AUG2022_MAIN