Automating table/object name scan and search in SA

Question

OK I'll start with the problem: I have product tables being created every week which are named in the format:

products_20130701
products_20130708
.
.
.

I'm trying to automate some campaign analysis so that I don't have to manually change the table name in the code every week to use whichever product table is the first one after the maximum end date of my campaign.

e.g

%put &max_enddate.; 
/*20130603*/

my product tables in June are:

products_20130602
*products_20130609*
products_20130616
products_20130623

in this instance i would like to use the second table in the list, ignoring over 12 months worth of product tables and just selecting the table who's date is just after my max_enddate macro.

I've been Googling all day and I'm stumped so ANY advice would be much appreciated.

Thanks!

Answer 1

First, get all possible tables:

data PRODUCT_TABLES;
    set SASHELP.VTABLE (keep=libname memname);
    *get what you need, here i keep it simple;
    where lowcase(substr(memname,1,9))='products_';
run;

Next, sort it by date, easily done due to the format of your dataset names.

proc sort data=PRODUCT_TABLES;
    by memname;
run;

Finally, you just need to get out the first record where the date is large enough.

data _NULL_;
    set PRODUCT_TABLES;
    *compare to your macro variable, note that i keep it as simple as possible and let SAS implicitly convert to numeric;
    if substr(memname,10,18)>=symgetn("max_enddate") then do;
        *set your match into a macro variable, i have put together the libname and memname here;
        call symput("selectedTable",cats(libname,'.',memname));
        stop; *do not continue, otherwise you will output simply the latest dataset;
    end;
run;

Now you can just put the macro variable when you want to use the appropriate dataset, e.g.:

data SOME_TABLE;
    set &selectedTable.;
    /*DO SOME STUFF*/
run;

Answer 2

A SQL solution:

data product_20130603;
run;

data product_20130503;
run;

data product_20130703;
run;

%let campdate=20130601;

proc sql;
  select min(memname) into :datasetname from dictionary.tables 
  where libname='WORK' and upcase(scan(memname,1,'_'))='PRODUCT' and
  input(scan(memname,2,'_'),YYMMDD8.) ge input("&campdate.",YYMMDD8.);
quit;

Now you have &datasetname that you can use in the set statement, so

    data my_analysis;     set &datasetname;     (whatever you are doing);     run;

Modify 'WORK' to the appropriate libname, and if there are any other restrictions add those as well. You might get some warnings about invalid dates if you have product_somethingnotadate, but that shouldn't matter.

The way this works - the dictionary.tables is a list of all tables in all libnames you have accessed (same as sashelp.vtable, but only available in PROC SQL). First this selects all rows that have a name with a date greater than or equal to your campaign end date; then it takes the min(memname) from that. Memname is of course a string, but in strings that are identical except for a number, you can still use min and get the expected result.

Answer 3

This is probably not suitable for your application, however I find it very useful for the datasets I have as they absolutely must exist for each Sunday and I evaluate the existence of the dataset at the beginning of my code. If they don't exist then it sends an email to our IT guys that tells them that the file is missing and needs to be re-created\restored.

%LET DSN = PRODUCTS_%SYSFUNC(PUTN(%SYSFUNC(INTNX(WEEK.2,%SYSFUNC(INPUTN(&MAX_ENDDATE.,YYMMDD8.)),0,END)),YYMMDDN8.));

With the other suggestions above they will only give you results for datasets that exist, therefore if the one you should have been using has been deleted then it will grab the next one and run the job regardless.