topic Problem using %let macro in Proc Export filename in SAS Programming

Problem using %let macro in Proc Export filename

MikeSimmos — Tue, 30 Aug 2011 23:41:28 GMT

Hi,

I export a report to Excel daily, with the data in the filename changing each day as follows in an extract of the full code:

%let endDate=20110830;

proc export data=summarylessgst1 outfile='H:\Output\Summary &endDate..xls'

dbms=excel replace;

run;

I had no problem with this when using SAS 9.1, but now I have moved to SAS 9.2, the output does not recognise &endDate., and instead outputs a file named:

'Summary &endDate..xls'

&endDate works fine in the rest of the code, i.e. when only selecting transactions with a date before &endDate. Can anyone help to provide an explanation and/or solution as to why the proc export outfile does not replace &endDate with the specified date?

Thanks

Problem using %let macro in Proc Export filename

art297 — Tue, 30 Aug 2011 23:50:35 GMT

I'm surprised that it worked correctly in 9.1. Enclose your filename in double quotes or else the macro variable won't resolve. I.e., use:

proc export data=summarylessgst1 outfile="H:\Output\Summary &endDate..xls"

dbms=excel replace;

run;

Problem using %let macro in Proc Export filename

MikeSimmos — Wed, 31 Aug 2011 01:33:31 GMT

Thanks art297, I changed to double quotations and it's now reading the macro variable properly. Yes I never thought that would be the issue since single quotations has always worked in SAS 9.1.

Glad it's resolved, thanks again for your help.

Problem using %let macro in Proc Export filename

DouglasMartin — Wed, 31 Aug 2011 13:23:11 GMT

Oh, really? Even in SAS 9.1, macro variables inside single quotes aren't supposed to be resolved. Are you sure you are remembering correctly?

Problem using %let macro in Proc Export filename

Peter_C — Wed, 31 Aug 2011 20:51:20 GMT

like with like it's hard to see how it works in 9.1

However, macro variable DATE referenced like '&date'd (holding a date like 31-Aug-2011) resolves OK for me, when it is within a longer double-quoted string

Problem using %let macro in Proc Export filename

PQK — Thu, 10 Nov 2011 00:19:47 GMT

MikeSimmos & art297,

Thanks for posting this question and answer. I was attempting to do a similar operation and could not figure out how to get a macro variable to correctly work with in an ouput filename.

PQK

Problem using %let macro in Proc Export filename

Tom — Thu, 10 Nov 2011 11:43:44 GMT

The outer most quotes are the ones that determine if macro references are resolved.

Try:

%let date1=01JAN2011;

%let x1="The date value is '&date1' ";

%let x2='The date value is "&date1" ';

%put x1=%superq(x1);

%put x2=%superq(x2);

Problem using %let macro in Proc Export filename

Peter_C — Fri, 11 Nov 2011 10:53:43 GMT

yep

it's all about the outside quoting!

(except.

( unless,

(are you adding something >>>>?

(why use superq?

(in what context does this need superq?

(%put x2=&x2 * demonstrate context 'where "&date1" does not resolve';

(%put x1=&x1 * demonstrate context "where '&date1' does resolve";