0

I am using sas DI studio and I am trying to get a date value written within the name of the input file,to put the same date also in the output file name. So first I need to get the filename and the extract the date and then put the date back. What is the easiest approach?

Joe
  • 62,789
  • 6
  • 49
  • 67
Aritra Basu
  • 43
  • 1
  • 8

2 Answers2

0

The best way to automize your SAS code is bringing the date information from the system like:

data _null_;
   call symput("PERDATE", Compress(put(intnx('month',date(),-1,'end'), YYMMDDn8.)));
run;

From the line above you get Perdate Macro variable in YYYYMMDD format for the previous month (-1). Then you can substract from this variable however you want. 

%let ayear  = %Substr(&PERDATE,1,4);   /* ayear is the year in YYYY format */
%let amonth = %Substr(&PERDATE,5,2);   /* amonth is the month in MM format */
%let aday   = %Substr(&PERDATE,7,2);  /* aday is the day in DD format    */
%let adate=&ayear&amonth;           /* adate is if you want in format YYYYMM */ 
%put &ayear&amonth;
%put &amonth;
%put &perdate;

Then you can read your input file daily or monthly basis automatically without changing the INPUT and OUTPUT file names. For example, if you get Sales report monthly basis in a format like Sales_YYYYMM then you can just write in your code like:

infile sales_&ayear.&amonth.;  /* Put Dot after each Macro statement */

Sales_&ayear.&amonth. will give you Sales_201506. You can describe your output file in a same matter like Out=result_&ayear.&amonth.;

data result_&ayear.&amonth.;
   set Sales_&ayear.&amonth.;
run;

If you are not getting your file regularly, if you need to enter the date manually, then you can just write create a macro variable for date at the beginning of your code, then you always use that macro variable in your code:

%let mydate=201506;
%put &mydate;

So in each run, you just change the variable mydate in yoru code. You don't need to change anything else...

user3714330
  • 679
  • 12
  • 32
  • The challenge is I want to retrieve the date from file metadata in sas DI...i.e. when the date part changesfor input file, the output file name will change accordingly and also the job won't need to be reconfigured. – Aritra Basu Jul 02 '15 at 18:47
0

The trick is setup the EXTERNAL FILE Metadata for input and output file correctly. Here's how to do that. Try creating EXTERNAL FILE metadata using an already existing file so that metadata of the columns are created correctly with format and informat. After EXTERNAL FILE metadata is created, edit the EXTERNAL FILE metadata by right clicking the FILE metadata and goto Properties -> File Location tab. In the file name section instead of file that already exist replace the date part with the macro variable. For example, if file name is : c:\Sushil_20150701.txt enter c:\Sushil_&mydatevar..txt . Also in FILE NAME QUOTING section, select "Double quotes around file name"

The same goes for Output file. Since we are going to use the &mydatevar macro variable on the input file name in the output file name, you can put the mydatevar similarly as done above.

After all this is done, we are good to use the file metadata with File Reader and File Writer transformation to read and write the files respectively.

Hope this helps!

sushil
  • 1,576
  • 10
  • 14
  • The file writing part should be exactly as you said- no doubt. But what about the input file name? I mean how do I set the value of input date from file metadata?(suppose filename is c:\Sushil_20110728..txt) – Aritra Basu Jul 02 '15 at 18:35
  • well, you could add your logic in the precode section of the job flow properties if you want the date value in the macro variable to be accessible to other transformation. So as per my answer the following become the filename : c:\Sushil_&mydatevar..txt and mydatevar macro variable can be added in pre-code e.g. %let mydatevar=20110728; . If things are not that simple and a logic needs to be put in place to get the input date we can code the logic in pre-code section itself to set value for mydatevar. – sushil Jul 02 '15 at 22:59