topic Re: Using Macro to Export to Excel in SAS Programming

Using Macro to Export to Excel

deleted_user — Tue, 07 Oct 2008 17:01:27 GMT

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?

Re: Using Macro to Export to Excel

sbb — Tue, 07 Oct 2008 18:08:40 GMT

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

Re: Using Macro to Export to Excel

deleted_user — Tue, 07 Oct 2008 19:55:22 GMT

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.

Re: Using Macro to Export to Excel

advoss — Mon, 06 Jul 2009 13:10:36 GMT

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

Re: Using Macro to Export to Excel

SpaceMonkey — Thu, 11 May 2017 01:40:30 GMT

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.

Re: Using Macro to Export to Excel

ChrisHemedinger — Thu, 11 May 2017 16:52:20 GMT

@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.

Re: Using Macro to Export to Excel

LisaGawlick — Wed, 20 Sep 2017 20:47:42 GMT

Chris, is there a reason why DBMS=XLSX instead of DBMS=excelcs works? We were having a similar issue where the Excel file wasn't being created at all. We moved from a PC-SAS version of our code to a Server environment using EG and the same Proc Export code running in the Server SAS was not creating the Excel output file at all (nor throwing any errors). We had been told in the past to use the DBMS=excelcs option because of our setup and the version of MS office that we use. I was able to get the first sheet of the excel file created by using your suggestion. Any insights would be appreciated! Thanks, Lisa