topic Re: Export Output &sysdate -1 filename in SAS Programming

Export Output &sysdate -1 filename

breelloyd — Wed, 16 Apr 2014 03:57:00 GMT

Hi Guys,

I'm trying to export a file and bring in yesterdays date into the file name.

Traditionally, we name files using the extract date- but we've been asked to do something differently this time.

Here is my code:

proc export dbms=csv

data=WORK.data

outfile="S:\D\D\ &sysdate..csv"

replace

label;

run;

How can I dynamically do this? ie &sysdate-1 for example.

so if I ran the file today, the file name would be:

15Apr14.csv

Thanks in advance

Re: Export Output &sysdate -1 filename

SKK — Wed, 16 Apr 2014 04:36:11 GMT

Try

%PUT %SYSFUNC(PUTN(%sysevalf(%SYSFUNC(TODAY())-1),DATE9.));

Re: Export Output &sysdate -1 filename

breelloyd — Wed, 16 Apr 2014 04:39:16 GMT

How would that look in my program?

proc export dbms=csv

data=WORK.data

%PUT %SYSFUNC(PUTN(%sysevalf(%SYSFUNC(TODAY())-1),DATE9.));

outfile="S:\D\D\ &sysdate..csv"

replace

label;

run;

I'm pretty new to coding in SAS

Re: Export Output &sysdate -1 filename

breelloyd — Wed, 16 Apr 2014 04:51:45 GMT

Actually, I worked it out...

%PUT date = %SYSFUNC(PUTN(%sysevalf(%SYSFUNC(TODAY())-1),DATE9.));

proc export dbms=csv

data=WORK.data

outfile="S:\D\D\DData_&date..csv"

replace

label;

run;

Re: Export Output &sysdate -1 filename

Tom — Wed, 16 Apr 2014 05:01:17 GMT

In your program you are referencing the value of a macro variable by using &SYSDATE.

You could use also reference a macro function call. In particular the %SYSFUNC() macro function that lets you reference anyone of many SAS functions in macro code.

proc export

dbms=csv

data=WORK.data

outfile="S:\D\D\%sysfunc(putn("&sysdate9"d-1,date.)).csv"

replace

label

;

run;

There are a couple of things to watch out for here.

1) I changed from using SYSDATE (which is formatted with a two digit year) to SYSDATE9 (which is the same value formatted with a four digit year).

2) I used the date literal syntax "ddMMMyyyy"d to convert the string that is in the SYSDATE9 macro variable back into a valid SAS date value and then subtracted one to get "yesterday".

3) I used the DATE format so that the value will look like what you used before (15APR14) but I strongly recommend using a flavor of YYMMDD format when putting date strings into filenames. These values will sort naturally into date order when sorted as character strings. If you use ddMMMyy format then the order in your directory of files will look almost random.

4) Note that SYSDATE is the date that SAS started and might not be today's date. If the session starts right before midnight or has been running a long time. If this is being run by users from interactive sessions then you are better off using the DATE(), or its alias TODAY(), function instead.

5) You could create a macro variable with the formatted date of choice and then the coding is a little clearer.

%let yesterday=%sysfunc(putn(%sysfunc(date())-1,yymmdd10));

proc export

dbms=csv

data=WORK.data

outfile="S:\D\D\&yesterday..csv"

replace

label

;

run;

Re: Export Output &sysdate -1 filename

SKK — Wed, 16 Apr 2014 05:08:51 GMT

Yep you are right, you can even supply your own values also.