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
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
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;
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
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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.