Get data set with maximum date in name by proc SQL

Question

Suppose I have some data sets in library lib, their names look like Table_YYYYMMDD (e.g. Table_20150101). I want to get a name of a data set with maximum date (YYYYMMDD) and store it in a macro variable.

I'm using proc sql and from dictionary.tables. First I extract a YYYYMMDD part of name. Then I should convert it to date and then find MAX. And I want to be sure that I have at least one data set in library.

proc sql;
    select put(MAX(input(scan(memname, 2, '_'), yymmdd8.)), yymmddn8.)
        into :mvTable_MaxDate
    from dictionary.tables
    where libname = 'LIB';
quit;

So,

1. Is it right to use sas functions like scan in proc sql?

2. How could I check whether the query is not empty (mvTable_MaxDate hasn't missing value)?

Thanks for your help:)

Answer 1

The cause of the error is that your are using the INPUTN() function, which expects the second argument to be a text literal or the name of a variable. If you change to INPUT(), it will avoid the error.

Also note you need to upcase the literal value of the library name on your where clause. Dictionary.tables stores libnames in upcase.

As written, the value of the macro variable will be a SAS date value. If you want it formatted as YYMMDDN8. you will need to add that.

Here's an example:

74   data a_20151027
75        a_20141022
76        a_20130114
77   ;
78   x=1;
79   run;

NOTE: The data set WORK.A_20151027 has 1 observations and 1 variables.
NOTE: The data set WORK.A_20141022 has 1 observations and 1 variables.
NOTE: The data set WORK.A_20130114 has 1 observations and 1 variables.

80
81   proc sql noprint;
82       select COALESCE(MAX(input(scan(memname, 2, '_'), yymmdd8.)), 0)
83           into :mvTable_MaxDate
84       from dictionary.tables
85       where libname = 'WORK';
86   quit;

87
88   %put &mvTable_MaxDate;
20388
89   %put %sysfunc(putn(&mvTable_MaxDate,yymmddn8));
20151027

As a side-comment, often life becomes much easier if you can just combine all your data into one dataset, and store the dataset name date suffix as a variable.