Generating Multiple Excel Files

The macro is dynamic to generate multiple Excel files containing the frequency results for each unique BY-group. (e.g., Type).

In this SAS example,  PROC SQL SELECT code is embedded inside a macro.

The SELECT query processes the column (passed in the macro parameter) then create a macro variable with the number of unique (distinct) value in the column, and a macro variable with the list of unique values separated with “-”

The macro uses the <a href="https://www.9to5sas.com/proc-freq-in-sas/">PROC FREQ</a> procedure, an iterative macro %DO statement, a %SCAN function, and WHERE= data set option to dynamically send the results to an Excel spreadsheet for each BY-group.

/*=====================================================================
Program Name            : MULTEXCELFILES.sas
Purpose                 : Dynamically generate separate Excel spreadsheets 
              containing the frequency results for each unique BY-group
SAS Version             : 9.4
Input Data              : N/A
Output Data             : N/A
Macros Called           : N/A
Program Version #       : 1.0

=======================================================================

/*---------------------------------------------------------------------
Usage:

%multExcelfiles(ds=SASHELP.CARS, col=TYPE, 
        outpath=/path/);
        
/*---------------------------------------------------------------------
MACRO PARAMETERS
DS         SAS DATASET NAME(REQ).
COL        COLUMN NAME (REQ)
OUTPATH    OUTPUT PATH TO SAVE THE EXCEL FILES  
---------------------------------------------------------------------*/
options symbolgen symbolgen mprint;
%macro MULTEXCELFILES(DS,COL,OUTPATH);
        * Get the count of unique values in the column;
        proc sql noprint;
                        /* number of unique values */
            select count(distinct &col) into :mprodtype_cnt 
            from &ds order by &col;
            select distinct &col into :mprodtype_lst separated by 
                "-" /* list of product types */
                from &ds order by &col;
        quit;

        %do j=1 %to &mprodtype_cnt;
            ods Excel file="&outpath/%SCAN(&mprodtype_lst,&j,-).xlsx" 
                style=styles.barrettsblue options(embedded_titles="yes");
            title "&col. - %SCAN(&mprodtype_lst,&j,-)";
            * Frequency of the column;

            proc freq data=&ds(where=(&col="%SCAN(&mprodtype_lst,&j,-)"));
                tables &col.;
            run;
ods Excel close;
%end;
%put &mprodtype_lst;
%mend MULTEXCELFILES;
/******* END OF FILE *******/

%multExcelfiles(ds=SASHELP.CARS, col=TYPE,
outpath=/home/subhroster20070/examples);

Generating Multiple Excel Files

Download this source code.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.