SAS - Automation - Script needs to automatically switch to a date (named) specific folder

[hondahawkrider]

I have a script that has to switch to a different date based folder to pull the data each day... For example, today's (on 2/23/2016) the script needs to pull the file it needs to use is

C:\Prod\2016\GZ\02.23.16\data02_23_031500.tsv'

As you can see both the folder and filename are date specfic... I would like to have one script that can be scheduled to run every day (at 3 am - but goes to a different folder

I have used the following to get data fields I need ..

52         
53         yy=DATE ();
54         format yy MMDDYYP2.;
55         %let yy=%sysfunc(today(),MMDDYYP2.);
56         %put yy = &yy.;
yy = 02        
58         yy2=DATE ();
59         format yy2 DDMMYYP2.;
60         %let yy2=%sysfunc(today(),DDMMYYP2.);
61         %put yy2 = &yy2.;
yy2 = 23
79         xc=DATE ();
80         format xc MMDDYYP.;
81         %let xc=%sysfunc(today(),MMDDYYP.);
82         %put xc = &xc.;
xc = 02.23.16

However, when I attempt to use those variable for the values - I dies..  See below

118        filet =  "C:\Prod Ack Gen Time Data\2016\GZ\"
119        || xc
120        || "\"
121        || "data"
122        || yy
123        || "_"
124        || yy2
125        || "_"
126        || "031500"		
127        || ".tsv"
128        ;
129        
130        filet = compress(filet) ;
132        call symput('filepath', filet) ;
133        %put filepath = &filepath ;
filepath = C:\Prod\2016\GZ\20507.20507.20507.\data20507_20507_031500.tsv

The 20507 is the core value for 2/23/2016, it's just not pulling the values i need.. I know I'm close - but Im missing something..

Help

Jason

[Reeza]

Please post your actual code, rather than log, it's hard to read.

Also, you first part of path gets cut out (PROD vs PROD Ack Gen), how is that happening?

[hondahawkrider]

that was my issue... I shortened the initial directory from Prod Ack Gen to just PROD so as to not have spaces in my directory

[Reeza]

Looking at your log again, it looks like your mixing macro and data step code, so that may be why you're seeing issues. 

As I initially asked, can you post the code rather than the log.

[hondahawkrider]

Im about 99% certain I am mixing macro and data step... I can't post the code as it uses live data.. Im in the process of trying to clean it up (not currently generic)..

[hondahawkrider]

Here is the sanitized first part..

Please keep in mind I inherited this .... 

You have to run the script twice or certain variables give you the

WARNING: Apparent symbolic reference N not resolved.... 

So Im positive that Data/Macro processing is mixed... I am trying to pull it apart

The parameters i need to change everyday are just

begdt = '22FEB16 00:00:00.0'dt

enddt = '23FEB16 03:00:00.0'dt

(where it's the 22Feb16 (yesterday) and 23Feb16 (today) change based upon what day the script is run)

&

filename filein (
   'C:\PROD\2016\GZ\02.23.16\data02_23_031500.tsv' 

(where it's the 02.23.16, and the 02_23 changes to the current day the script is run)

It's those 3 lines that are currently changed everyday for the (rest of) the script to run manually. Im trying to finish anothers work to make it in SAS so that one script can automatically be run each day without having to make changes...

It doesn't matter how I get there..  But this is what I have so far ... (yes, there are some duplicate values, I have been trying to figure it all out - but no such luck)

options obs=10000000;
*%include "C:\PROD\Automate\AutoConfig.sas" ;

data _null_ ;

*x = today() ;
x=TODAY ();
format x date.;
%let x=%sysfunc(today(),date.);
%put x = &x.;
call symput('rundate','x') ;
run;
%put rundate  = &rundate ;


data _null_ ;
x=TODAY ();
format x date.;
%let x=%sysfunc(today(),date.);
%put x = &x.;
call symput('rundate','x') ;
%put rundate  = &rundate ;

*set Yesterday's Date;
y=x-1;
format y date.;
%let y = %sysfunc(cats(%sysfunc(intnx(day,%sysfunc(Date()), -1), date.)));
%put y = &y.;

yy=DATE ();
format yy MMDDYYP2.;
%let yy=%sysfunc(today(),MMDDYYP2.);
%put yy = &yy.;

yy2=DATE ();
format yy2 DDMMYYP2.;
%let yy2=%sysfunc(today(),DDMMYYP2.);
%put yy2 = &yy2.;

yy3=DATE ();
format yy3 YYMMDD2.;
%let yy3=%sysfunc(today(),YYMMDD2.);
%put yy3 = &yy3.;

xx=DATE ();
format xx YEAR.;
%let xx=%sysfunc(today(),YEAR.);
%put xx = &xx.;

xa="'" || " 00:00:00.0'dt" ;
ya="'" || " 03:00:00.0'dt" ;

xb=" 00:00:00.0'dt" ;
yb=" 03:00:00.0'dt" ;

xc=DATE ();
format xc MMDDYYP.;
%let xc=%sysfunc(today(),MMDDYYP.);
%put xc = &xc.;

/*
DD=put(&xc,MMDDYYP);
call symput('dotted',DD) ;
*/

yc=xc-1;
format yc MMDDYYP.;
%let yc = %sysfunc(cats(%sysfunc(intnx(day,%sysfunc(Date()), -1), MMDDYYP.)));
%put yc = &yc.;

x1= "'" || put(&rundate-1,date.) || " 00:00:00.0'dt" ;
call symput('begdt',x1) ;

y1= "'" || put(&rundate,date.) || " 03:00:00.0'dt" ;
call symput('enddt',y1) ;

tmon = left(trim(month(&rundate))) ;
if length (tmon) = 1 then 
tmon = '0' || tmon ;
call symput('tmon2', tmon) ;
%put tmon2 = &tmon2 ;

tday = left(trim(day(&rundate))) ;
if length (tday) = 1 then 
tday = '0' || tday ;
call symput('tday2', tday) ;
%put tday2 = &tday2 ;

tyear = strip(year(&rundate)) ;
call symput('tyear4', tyear) ;
%put tyear4 = &tyear4 ;

tyear2 = substr(compress(tyear),3) ;
call symput('tyear2d', tyear2) ;
%put tyear2d = &tyear2d ;

dirt = "&localdir" ;

filet =  "C:\PROD\2016\GZ\" 
|| xc
|| "\"
|| "data"
|| yy
|| "_"
|| yy2
|| "_" 
|| "031500"		
|| ".tsv" 
;

filet = compress(filet) ;

call symput('filepath', filet) ;
%put filepath = &filepath ;
%let localdir=C:\PROD\Automate;
%let netdrive=C:\PROD\Automate;
%put localdir = &localdir ;
%put netdrive = &localdir ;
%put begdt = &begdt ;
%put enddt = &enddt ;

/*
%let begdt = '18Feb16 00:00:00.0'dt ;
%let enddt = '19Feb16 03:00:00.0'dt ;
*/
%let sync_int = 600;
%let ginterval='01:00:00.0't ; 


filename filein (
   'C:\PROD\2016\GZ\02.23.16\data02_23_031500.tsv'  
 );

/*
filename filein (
   &filet   
 );
*/
 run ;

[Reeza]

Honestly...I don't want to touch that. It may be better to start from scratch, if thats an option.

Post what you have and what you need

Need -> Generate file path that looks like X

Have -> Macro variable with path portion

Rules -> Date, datetime and need to create directory or directory already exists.

[hondahawkrider]

hmm.. thats what I thought I did..

what I have put is things I have tried to use to get the variables ... Pretty much everything in the script that I put can disappear or be deleted if need be..

Basically when I run it manually I have the following code to change

%let begdt = '22Feb16 00:00:00.0'dt ;

%let enddt = '23Feb16 03:00:00.0'dt ;

%let sync_int = 600;

%let ginterval='01:00:00.0't ;

filename filein (

'C:\PROD\2016\GZ\02.23.16\data02_23_031500.tsv'

 );

So for today - ie the 24th - I have to make the following

%let begdt = '23Feb16 00:00:00.0'dt ;

%let enddt = '24Feb16 03:00:00.0'dt ;

%let sync_int = 600;

%let ginterval='01:00:00.0't ;

filename filein (

'C:\PROD\2016\GZ\02.24.16\data02_24_031500.tsv'

 );

In the first line

I change 22Feb16 to 23Feb16... It needs to be the previous day's date

In the 2nd line

I change 23Fed16 to 24Feb14 - it needs to be today's date

and in the file name

I have to change the Folder name freom 02.23.16 to 02.24.16 and the file name from data02_23_ to data02_24_

Basically it's a script that I run daily (manually). I am trying to use SAS to detect what day it is when it runs, and have it change those date fields - so I can set it to run on my SAS server each day without having to change any code... I was trying to use what was avabilable to me - but as you can see I am struggling with it...  Ive been handed the script and am stuck..

Any help is greatly appreciated....  I tried the best I could to see if I could figure it out - but haven't figured it out yet

[Kurt_Bremser]

First, you've got a problem by mi(a)ngling data step code and macro code.

Second, you've got a problem because of missing formatting of date values.

data _null_;
xx = date();
yy = dhms(intnx('day',xx,-1),0,0,0);
zz = dhms(xx,3,0,0);
length
  this_day this_month this_year $2
  this_year4 $4
  myfilename $100
;
this_day = put(day(xx),z2.);
this_month = put(month(xx),z2.);
this_year4 = put(year(xx),z4.);
this_year = substr(this_year4,3);
myfilename = 'C:\Prod\' !! this_year4 !!'\GZ\' !! put(xx,mmddyyp8.) !! '\data' !! this_month !! '_' !! this_day !! '_031500.tsv';
call symput('filepath',strip(myfilename));
call symput('begdt',"'"!!put(yy,datetime18.1)!!"'dt");
call symput('enddt',"'"!!put(zz,datetime18.1)!!"'dt");
run;

%put filepath=&filepath;
%put begdt=&begdt;
%put enddt=&enddt;

Edited to include begin and end datetime values.

[hondahawkrider]

This totally rocks ...

It does the filename path stuff fantastically...

The  only thing I have to do is incorporate how to also change

%let begdt = '23Feb16 00:00:00.0'dt ;

%let enddt = '24Feb16 03:00:00.0'dt ;

so I get yesteday's and todays date in that format..

You given me a lot of food for thought.. Thanks 

[Kurt_Bremser · Accepted solution]

Edited my post to also create the timestamps.

My example is how you use the data step capabilities to create values in an easy-to-read way, and then hand them over to the macro facility.

You could do most of that with pure macro code, but that tends to become very hard to read and maintain.

[hondahawkrider]

That works Great!!

Thanks