Macro to export dataset to Excel with filename var

[deleted_user]

Hello -- has anyone used a macro to export a dataset to Excel, using the export filename as a variable? I've started code below but then get this error,

"170! ".xls"
-
49
170! )
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended
---------------------------------------------------------------------

%let yrmon = '200909';
%let dt = '091409';
%macro exportxl;
PROC EXPORT DATA= WORK.YRMON_ALL2SUM
OUTFILE= concat("I:\1 Commissions\PPA\Audit\", &yrmon, "\test2_" , &dt , ".xls")
DBMS=EXCEL REPLACE;
SHEET="test2";
RUN;
%mend exportxl;

[DanielSantos]

Hello.

Try this instead:

%let yrmon = '200909';
%let dt = '091409';
%let OUTFILE=I:\1 Commissions\PPA\Audit\&YRMON\test2_&DT..xls;
%macro exportxl;
PROC EXPORT DATA= WORK.YRMON_ALL2SUM
OUTFILE= "&OUTFILE"
DBMS=EXCEL REPLACE;
SHEET="test2";
RUN;
%mend exportxl;

You cannot reference the use of any functions (beside macro ones) for the procedure arguments. So in the example above, the OUTFILE expression is build into a macro var before the actual procedure call.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[deleted_user]

Thanks Daniel. Does anyone know if there is an enhancement being planned to be able to use functions in procedure arguments? If not, is there somewhere I can submit an enhancement request?

[deleted_user]

P.S. The other approach I tried was to set the filename value in the %let statement as you have indicated, but concatenated with a nested macro variable using ||. That approach didn't work, such as the following -- perhaps for similar reason that the concat errored out?
______________________________________
%let yrmon = '2009_09';
%let filenm = 'I:\1 Commissions\PPA\Audit\' || &yrmon || '\insues_091409.xls';

-------------------------------------------------------------------

Then, when the above value &filenm is used in the proc export, the following error is issued:

230 %macro exportxl (setnm = );
231 PROC EXPORT DATA= &setnm
232 OUTFILE= &filenm
233 DBMS=EXCEL REPLACE;
234 SHEET=&setnm;
235 RUN;
236 %mend exportxl;
237
238 %exportxl (setnm = p2);

1 'I:\1 Commissions\PPA\Audit\' || '2009_09' || '\insues_091409.xls'
ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBMS, FILE, OUTFILE,
OUTTABLE, REPLACE, TABLE.

[sbb]

For my visual-programming interests/needs, I tend to lean on the TRANWRD function using a macro variable to substitute of a pattern-string with a template variable being referenced - as shown below:

%LET XOUTFILE = BIG.WOOLY.MONTHLY.DATA.Myyyymm.CSV;
* PROC SQL or DATA STEP (SYMPUT) code to generate &PERIOD. ;
* ...the outcome is demonstrated below. ;
%let period = 200909;
%LET OUTFILE = %SYSFUNC(TRANWRD(&XOUTFILE,yyyymm,&PERIOD));
%put _user_;


Scott Barry
SBBWorks, Inc.

[deleted_user]

I think I got it -- I didn't know about using period as concatenator.

%macro exportxl (librf, setnm);
PROC EXPORT DATA= &librf..&setnm
OUTFILE= "&prefixnm.&filenm..xls"
DBMS=EXCEL REPLACE;
SHEET=setnm;
RUN;
%mend exportxl;

%let prefixnm = I:\1 Commissions\PPA\Audit\2009_09\;
%let filenm = \insues_091409;
%exportxl(ppabon08,p2);

[PatrickG]

I believe that if you call Tech Support and explain what you need, they can submit an enhancement request for you and route it to the proper group(s) for consideration.

[sbb]

Enhancement? The use of %SYSFUNC opens up the use of DATA STEP functions within the macro language. From the original OP, the function used CONCAT was invalid and unnecessary within the macro language. The CAT function coded within a %SYSFUNC( ) would have worked fine or the use of macro variables as was demonstrated.

Scott Barry
SBBWorks, Inc.