topic Re: how to split excel export into separate sheets in SAS Data Management

how to split excel export into separate sheets

jmckenzie — Fri, 11 Sep 2015 15:57:11 GMT

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.

Re: how to split excel export into separate sheets

DBailey — Fri, 11 Sep 2015 18:35:31 GMT

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

Re: how to split excel export into separate sheets

Fugue — Fri, 11 Sep 2015 18:37:33 GMT

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.

Re: how to split excel export into separate sheets

jmckenzie — Tue, 22 Sep 2015 20:38:43 GMT

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