DATA Step, Macro, Functions and more

Automating Data Pulls Based on Data Name

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Automating Data Pulls Based on Data Name

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!


Accepted Solutions
Solution
‎10-16-2014 02:40 AM
SAS Employee
Posts: 15

Re: Automating Data Pulls Based on Data Name

Posted in reply to dangoebel

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

View solution in original post


All Replies
Solution
‎10-16-2014 02:40 AM
SAS Employee
Posts: 15

Re: Automating Data Pulls Based on Data Name

Posted in reply to dangoebel

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

Super Contributor
Posts: 308

Re: Automating Data Pulls Based on Data Name

Posted in reply to dangoebel

Hello,

If the data is created on working days :

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

%put &rundate;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 197 views
  • 0 likes
  • 3 in conversation