Find the table whose name has the latest date

[rhapsody]

This is what I have

libname tables "c:\Data";

In the libname "tables" I have many tables looking like this (this is just part of all the tables, there are at least a 100 more with the same name format)

result_201103_credit

result_201106_credit

result_201109_credit

result_201112_credit

result_201903_credit

I want to write a sas code that extracts the latest date and turns it into a macro variable. So when I write

%put &date.;

it returns

201903

[Jagadishkatam]

Consider that you have the library tables, then you can try the below code

proc sql;
create table test as select memname from dictionary.tables where libname='TABLES';
quit;


proc sql;
select max(input(scan(memname,2,'_'),best.)) as memname2 into: date from table ;
quit;

%put &date;

[yabwon]

Hi,

try something like this:

/* example data */
data 
  result_201103_credit
  result_201106_credit
  result_201109_credit
  result_201112_credit
  result_201903_credit
;
x = 42;
run;

libname tables (work);

/* get the dataset names */
proc contents 
  data = tables._all_
  memtype = DATA
  OUT=SAS_data_set(keep = memname where=(memname like 'RESULT%CREDIT')) /* names are "upcassed" */
;
run;

/* keep only digits in names and get the max value */
proc sql;
  select 
    max(compress(memname, , "KD"))
  into 
    :date trimmed
  from 
    SAS_data_set
  ;
quit;

%put *&date.*;

all the best

Bart

[ErikLund_Jensen · Accepted solution]

Hi @rhapsody 

You could write a macro to give the wanted result. The code is based on the good advices you have got so far:

%macro getlasttable;
	%if not %symexist(date) %then %global date;
	%let date =;
	proc sql noprint;
			select max(scan(memname,2,'_')) into :date
			from dictionary.tables
			where 
				libname = upcase("tables")
				and memname like 'RESULT%CREDIT';
	quit;
%mend;

%getlasttable;
%put &=date;

201903

[dario_medina]

There is a paper that you might find useful : Automate in a Dash with SAS: Time saving techniques for building quality improvement dashboards. Starts by capturing all file names from a folder and getting the latest named file.
Hope it helps