Search
 
 

Display results as :
 


Rechercher Advanced Search

Keywords

Affiliates
free forum


Automatically Separating Data into Excel Sheets

View previous topic View next topic 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

View previous topic View next topic Back to top

- Similar topics

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