How do i find and use the most recent table based on the name

[rngsh001]

Hi there,

How do i scan a particular library to use the latest table based on their name.  In this example, library is toshare and tables all start with extdate_. 

I found this macro from another post which works when date format is yyyymmdd, but not for ddmmmyyyy. 

%macro getlasttable;

%if not %symexist(date) %then %global date;

%let date =;

proc sql noprint;

select max(scan(memname,-1,'_')) into :date

from my_contents

where 

libname = upcase("toshare")

and memname like 'EXTDATE_%';

/* format date ddmmmyyyy.;*/

quit;

%mend;

Thanks for your help.

[Patrick · Accepted solution]

Below should do.

libname toshare "%sysfunc(pathname(work))";
data 
  toshare.extdate_01apr2023
  toshare.extdate_01may2023
  toshare.extdate_01jun2023
  ;
  set sashelp.class;
run;

%let last_tbl_dt=;
proc sql noprint;
  select 
    max(input(scan(memname,-1,'_'),date9.)) format=date9.
      into :last_tbl_dt trimmed
  from dictionary.tables
  where 
    libname = 'TOSHARE'
    and memname like 'EXTDATE^_%' escape '^'
  ;
quit;

data want;
  set toshare.extdate_&last_tbl_dt;
run;

[rngsh001]

Thanks Patrick, worked like a charm. Much appreciated.