BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
7 REPLIES 7
DanielSantos
Barite | Level 11
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
Not applicable
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
SAS Employee
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4144 views
  • 0 likes
  • 4 in conversation