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.

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.