SAS - Choose last dataset in library that satisfies specific name convention

Question

Say I have the a library named mylib.

Within the mylib library, the following datasets are held:

mylib.data_yearly_2015
mylib.data_yearly_2016
mylib.data_yearly_2017
mylib.data_yearly_2018
mylib.data_yearly_2015
mylib.data_mtly_01JUN2015
mylib.data_mtly_01DEC2015
mylib.data_mtly_01JUN2016
mylib.data_mtly_01DEC2016
mylib.data_mtly_01JUN2017
mylib.data_mtly_01DEC2017

Now I need to write a macro that will specifically choose the latest data_mtly_xxxxxx table from the mylib library.

For example, in the current stage, it should choose mylib.data_mtly_01DEC2017

If, however, a new dataset gets added, for example mylib.data_mtly_01JUN2018, it would have to choose that table.

How can I go about doing this in SAS?

Answer 1

1. Get a list of all data sets
2. Get the date portion using SCAN() and INPUT()

3. Get max date.

   Proc sql noprint;
   
   Select max(input(scan(name, -1, ‘_’), date9.) ) into :latest_date
   From sashelp.vtable
   Where upcase(libname) = ‘MYLIB’ and upcase(memname) like ‘DATA_MTLY_%’;
   
   Quit;
   

Now you should have the latest date value in a macro variable and can use that in your code.

   %put &latest_date.;

If it looks like a number and not a date, you’ll need a format applied but you should be able to convert it using PUT().

Note: code is untested.