how to split excel export into separate sheets

[jmckenzie]

I have an excel file in which two of the fields are 'Area' and 'Chart_Field_1'.  I would like each Area to be exported to an individual Excel file, and for each Chart_Field_1 within that Area to have its own sheet.  Any suggestions are greatly appreciated.  Thanks.

[DBailey]

You might end up with different export statements for each area/chart field option.  In the current version of SAS, you can send data to the same file but different sheets by using the "sheet=" option.

something like

proc export data=srcdata (where=(area='Area1' and chart_field_1='c1')) file=a1 dbms=xlsx replace; sheet='c1';

proc export data=srcdata (where=(area='Area1' and chart_field_1='c2')) file=a1 dbms=xlsx replace; sheet='c2';

proc export data=srcdata (where=(area='Area2' and chart_field_1='c1')) file=a2 dbms=xlsx replace; sheet='c1';

proc export data=srcdata (where=(area='Area2' and chart_field_1='c2')) file=a2 dbms=xlsx replace; sheet='c2';

[Fugue]

If you have a large number of values for "Area" and "Chart_Field_1", you can use a macro to loop through the possible values and output a corresponding Excel workbook for each value of "Area" containing separate spreadsheets for each value of "Chart_Field_1".

To give you some ideas, look at this post: https://communities.sas.com/t5/SAS-Procedures/Split-SAS-Output-into-multiple-Sheets/td-p/18996

If the number of variables is small, you could hard-code them.

[jmckenzie]

I have a large number of variables so I'll need to go the macro route. 

Not sure what I'm doing, this is what I have so far.  What am I doing wrong?

PROC IMPORT OUT= WORK.IDs DATAFILE= "G:\FinUnitReporting\IFASDEMIScombined.xlsx"
            DBMS=xlsx REPLACE;
     SHEET="sheet1";
     GETNAMES=YES;
RUN;

proc sql;
create table RawData as
 select *
 from work.IDs;

*;
%macro rpt(area);
Proc sql;
  Create table t1 as
Select * from RawData
Where area = “&area.”;
%mend;
*;
%put _user_;
/* for testing
%rpt(CF1);
*/
/*************************************************************
   loop processing
**************************************************************/
*;
proc sql;
  create table grp1 as
  select distinct area
  from  RawData
  order by 1;
quit;
filename loops TEMP;
DATA _NULL_;
FILE LOOPS;
SET grp1
  PUT '%rpt (' area  ');' / '*;';
run;
*;
%inc loops;
*;