SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to split excel export into separate sheets

Reply
Occasional Contributor
Posts: 10

how to split excel export into separate sheets

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.

Super Contributor
Posts: 578

Re: how to split excel export into separate sheets

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

Super Contributor
Posts: 307

Re: how to split excel export into separate sheets

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.
Occasional Contributor
Posts: 10

Re: how to split excel export into separate sheets

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

Ask a Question
Discussion stats
  • 3 replies
  • 370 views
  • 0 likes
  • 3 in conversation