Automatically Separating Data into Excel Sheets

From sasCommunity
Jump to: navigation, search

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. You can read more about writing data to Excel in the article SAS Excels by Gerald Frey.

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

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.

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

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.

The REPLACE option in PROC EXPORT does not work the same in V8 as it does in SAS9. Read about the differences here.

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