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 PROC FREQ 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;
select count(distinct &col) into :mprodtype_cnt /* number of unique values */
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);
