Automating Data Pulls Based on Data Name

[dangoebel]

The company I work for has a system that pulls in prices overnight and places them in a particular library. I've been working on automating regressions based on this data, but need to automate which date the regression pulls in order to make it work without daily updating. My beginning data step is below.

data work.regression;

     set prod_stg.prices_transpose_22sep2014;

The files will always be found in the prod_stg library and the data will always begin with prices_transpose_ followed by most recent date in date9 format. For example, data for today would be in prod_stg.prices_transpose_15oct2014. Does anyone know how to automate the date at the end of the data file to pull back the most recent data set? It also would need to be able to go back more than one day if data was missing (weekends, holidays, ect.)

Thanks!

[Vladislaff · Accepted solution]

%let lastdat=;

proc sql noprint;

select memname into :lastdat from dictionary.tables where

upcase(libname) = 'PROD_STG' and upcase(memname) like 'PRICES_TRANSPOSE_%'

order by input(scan(memname,-1,'_'),date.) desc;

quit;

data work.regression;

  set prod_stg.&lastdat;

run;

[Loko]

Hello,

If the data is created on working days :

%let rundate=%sysfunc(intnx(WEEKDAY,%sysfunc(today()),-1),date9.);

%put &rundate;