Generating Multiple Excel Files

0
10

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);

 

Generating Multiple Excel Files

Download this source code.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.