DATA Step, Macro, Functions and more

Using Macro to Export to Excel

Accepted Solution Solved
Reply
N/A
Posts: 0
Accepted Solution

Using Macro to Export to Excel

I created a macro to cycle through all of the fields in a SAS database, created a proc freq, outputed them to a SAS dataset and now I want to export these to excel to send e-mail. I want to put in a single excel file. I tried to name the sheet the same name as the field name, but it is giving me an error because I don't have quotes around the sheet name. Here is my code:

libname LOSSDATA 'G:\NA\Corp\CORPACT\APPS\PRICING\Data Management\AgentsDataQuality\LOSS INFO';
options mprint;
** Frequency Test Macro **;
%MACRO FREQ(DSET,FIELDNAME);
PROC FREQ DATA=&DSET NOPRINT;
TABLES &FIELDNAME / OUT=&FIELDNAME;
RUN;
%MEND;
** Export Macro **;
%MACRO DQ_EXPORT(DSET,XLSHT);
PROC EXPORT DATA= &DSET
OUTFILE= "G:\NA\Corp\CORPACT\APPS\PRICING\Data Management\AgentsDataQuality\Data Quality Tests\LossData\Frequency.xls" ;
DBMS=EXCEL REPLACE;
SHEET=&XLSHT;
RUN;
%MEND;

** Macro for Looping through each field **;
%MACRO FREQ_VAR_LIST(DSET);
PROC CONTENTS NOPRINT DATA=&DSET OUT=NAMETYPE(KEEP=NAME TYPE);
%LET DSID=%SYSFUNC(OPEN(NAMETYPE));
%LET NUM_OBS=%SYSFUNC(ATTRN(&DSID,NOBS));
%LET RC=%SYSFUNC(CLOSE(&DSID));

%LET DSID=%SYSFUNC(OPEN(&DSET,I));
%LET VARCOUNT=%SYSFUNC(ATTRN(&DSID,NVARS));


/* USe if statement if only some of the fields are required */

%DO I=1 %TO &VARCOUNT;
%LET VARNAME=%SYSFUNC(VARNAME(&DSID,&I));
%FREQ(&DSET,&VARNAME);
%LET SHTNAME=%QUOTE(&VARNAME);
%DQ_EXPORT(&VARNAME,&SHTNAME);
%END;
%LET RC=%SYSFUNC(CLOSE(&DSID));
%MEND;

** Macro to run Test for all fields in a given dataset **;
%FREQ_VAR_LIST(LOSSDATA.LOSSDATAFINAL);

Here is the error I am getting:

MPRINT(DQ_EXPORT): PROC EXPORT DATA= A01_COMPANY OUTFILE=
"G:\NA\Corp\CORPACT\APPS\PRICING\Data Management\AgentsDataQuality\Data Quality
Tests\LossData\Frequency.xls" ;
MPRINT(DQ_EXPORT): AEXC;
MPRINT(DQ_EXPORT): DBMS=EXCEL REPLACE;
NOTE: The previous statement has been deleted.
MPRINT(DQ_EXPORT): SHEET=A01_COMPANY;
NOTE: The previous statement has been deleted.
MPRINT(DQ_EXPORT): RUN;
NOTE: Line generated by the invoked macro "DQ_EXPORT".
1 DBMS=EXCEL REPLACE; SHEET=&XLSHT; RUN;

----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.


I am assuming it is because A01_COMPANY is not in quotes. How do I get A01_COMPANY in quotes?

Accepted Solutions
Solution
‎05-11-2017 12:55 PM
Super Contributor
Super Contributor
Posts: 3,174

Re: Using Macro to Export to Excel

To start, the DBMS= keyword should be part of the PROC EXPORT statement -- you have a semi-colon after the OUTFILE= parameter string. There also appears to be some extraneous character string "AEXC" -- maybe not. I believe that you can wrap your SHEET= parameter (resolved) in double-quotes, as well.

Scott Barry
SBBWorks, Inc.

SAS 9.2 PROC EXPORT DOC link:
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/a000316288.htm

View solution in original post


All Replies
Solution
‎05-11-2017 12:55 PM
Super Contributor
Super Contributor
Posts: 3,174

Re: Using Macro to Export to Excel

To start, the DBMS= keyword should be part of the PROC EXPORT statement -- you have a semi-colon after the OUTFILE= parameter string. There also appears to be some extraneous character string "AEXC" -- maybe not. I believe that you can wrap your SHEET= parameter (resolved) in double-quotes, as well.

Scott Barry
SBBWorks, Inc.

SAS 9.2 PROC EXPORT DOC link:
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/a000316288.htm
N/A
Posts: 0

Re: Using Macro to Export to Excel

It was the semicolon and not the quotes. I am not sure why it is putting the AEXC in the MPRINT, but I am not doing anything to write it to the code.
Frequent Contributor
Posts: 91

Re: Using Macro to Export to Excel

The AEXC is coming from MPRINT...see Usage Note 13860: AEXC returned by MPRINT when macros are used in PROC IMPORT for Excel
New Contributor
Posts: 4

Re: Using Macro to Export to Excel

Hi - I am currently having this problem. For some reason, when I use PROC EXPORT to Excel in a macro, I get the following line in the log:

 

MPRINT(EXPORT): AEXC;

 

... and no Excel file is created.

 

I cannot find any usage note with number 13860 by searching the SAS site. Can someone please provide me with a link to a fix?

 

Thank you.

Community Manager
Posts: 2,757

Re: Using Macro to Export to Excel

@SpaceMonkey: Here's the current link to the SAS Note: http://support.sas.com/techsup/notes/v8/13/860.html

 

However, that in itself should not cause problems with PROC IMPORT or producing the Excel file.  If you have SAS 9.4, I'd try DBMS=XLSX instead of DBMS=Excel -- you might be happier with the result.  If you can't get it working, I suggest that you create a different post with as much of the code and log that you can share.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 501 views
  • 0 likes
  • 5 in conversation