Automatically Separating Data into Excel Sheets

You can use the macro language with a PROC EXPORT to automatically generate a Excel workbook with one sheet for each level of a classification variable. In the following example we want to create a workbook (CLASS_AGES.XLS). We need to break up the data using AGE as a classification variable. Each age is to be shown on its own sheet in the workbook.

There are several ways to do this, however for this example we will create a series of macro variables; one for each level of the classification variable. This SQL step will create the macro variables &IDAGE1, &IDAG2, and so on up to 99 levels of AGE. The actual number of levels is stored in the automatic macro variable &SQLOBS.

proc sql noprint;
select distinct age
into :idage1 - :idage99
from sashelp.class;
%let agecnt = &sqlobs;
quit;


We then use a macro %DO loop to work through the list of AGE levels and create a PROC EXPORT for each, using a WHERE= data set option. Notice that the sheet name is changed for each of the values of the classification variable. &&IDAGE&I will resolve to the value of the variable AGE.

%do i = 1 %to &agecnt;
PROC EXPORT DATA = sashelp.class(where=(age=&&idage&i))
OUTFILE="C:\temp\class_ages.xls"
DBMS= excel
REPLACE;
sheet = "Age_&&idage&i";
RUN;
%END;


The full macro becomes:

%macro multisheet;
proc sql noprint;
select distinct age
into :idage1 - :idage99
from sashelp.class;
%let agecnt = &sqlobs;
quit;

%do i = 1 %to &agecnt;
PROC EXPORT DATA = sashelp.class(where=(age=&&idage&i))
OUTFILE="C:\temp\class_ages.xls"
DBMS= excel
REPLACE;
sheet = "Age_&&idage&i";
RUN;
%END;
%mend multisheet;
%multisheet

So easy.....