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?