Search
 
 

Display results as :
 


Rechercher Advanced Search

Keywords

Affiliates
free forum


Automatically Separating Data into Excel Sheets

Go down

Automatically Separating Data into Excel Sheets

Post  pallav on Fri May 18, 2012 11:19 am

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.....Smile

pallav

Posts : 98
Join date : 2012-03-14
Location : Ahmedabad

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum