topic Re: Automating Data Pulls Based on Data Name in SAS Programming

Automating Data Pulls Based on Data Name

dangoebel — Wed, 15 Oct 2014 20:10:58 GMT

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!

Re: Automating Data Pulls Based on Data Name

Vladislaff — Thu, 16 Oct 2014 06:40:51 GMT

%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;

Re: Automating Data Pulls Based on Data Name

Loko — Thu, 16 Oct 2014 09:49:21 GMT

Hello,

If the data is created on working days :

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

%put &rundate;