topic Re: Macro to export dataset to Excel with filename var in SAS Programming

Macro to export dataset to Excel with filename var

deleted_user — Mon, 14 Sep 2009 14:54:10 GMT

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;

Re: Macro to export dataset to Excel with filename var

DanielSantos — Mon, 14 Sep 2009 16:04:43 GMT

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

Re: Macro to export dataset to Excel with filename var

deleted_user — Mon, 14 Sep 2009 16:15:25 GMT

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?

Re: Macro to export dataset to Excel with filename var

deleted_user — Mon, 14 Sep 2009 16:37:49 GMT

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.

Re: Macro to export dataset to Excel with filename var

sbb — Mon, 14 Sep 2009 16:53:57 GMT

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.

Re: Macro to export dataset to Excel with filename var

deleted_user — Mon, 14 Sep 2009 19:52:36 GMT

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);

Re: Macro to export dataset to Excel with filename var

PatrickG — Thu, 17 Sep 2009 12:03:24 GMT

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.

Re: Macro to export dataset to Excel with filename var

sbb — Thu, 17 Sep 2009 12:24:51 GMT

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.