topic Re: How do i find and use the most recent table based on the name in SAS Programming

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

rngsh001 — Fri, 06 Oct 2023 00:20:00 GMT

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.

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

Patrick — Fri, 06 Oct 2023 01:15:56 GMT

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;

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

rngsh001 — Fri, 06 Oct 2023 01:33:53 GMT

Thanks Patrick, worked like a charm. Much appreciated.