Proc export BY to many excel sheets in same file

[Ronein]

Hello

I want to do proc export by  "Origin".

I want that automatically will be created a sheet for each Origin

I know to do it by preforming proc export for each "Origin" value but  my question is how to do it automatically?

(For example if there were 1000 values for "Origin" then it is not clever to way to write so many proc exports)

proc export data=sashelp.cars (where=(Origin='Asia'))
outfile="/path/cars.xlsx"
DBMS=xlsx replace;
sheet='Asia';
run;
proc export
data=sashelp.cars (where=(Origin='Europe'))
file="path/cars.xlsx"
DBMS=xlsx replace;
sheet='Europe';
run;
proc export
data=sashelp.cars (where=(Origin='USA'))
file="/path/cars.xlsx"
DBMS=xlsx replace;
sheet='USA';
run;

[Kurt_Bremser]

Step 1: identify the dynamic elements, and replace with macro variables:

%let origin=Asia;

proc export data=sashelp.cars (where=(Origin="&origin."))
outfile="/path/cars.xlsx"
DBMS=xlsx replace;
sheet="&origin.";
run;

Note double quotes instead of single quotes; single quotes prevent macro resolution.

Test if it works.

Step 2: wrap into a macro definition:

%macro excel_export(origin);
proc export data=sashelp.cars (where=(Origin="&origin."))
outfile="/path/cars.xlsx"
DBMS=xlsx replace;
sheet="&origin.";
run;
%mend;

%origin(Asia)

Test again.

Step 3: create a control dataset:

proc sort
  data=sashelp.cars (keep=origin)
  out=control
  nodupkey
;
by origin;
run;

and (step 4) use it to call the macro:

data _null_;
set control;
call execute('%nrstr(%excel_export(' !! strip(origin) !! '))');
run;

[Shmuel]

Unfortunately there is no option to use BY in PROC EXPORT.

You can automate your code by a macro program:

%macro export(dsnin);
        proc sql;
             create table origins as
             select distinct origin from &dsnin;
       quit;
       %let no_of_org = &sqlobs;

       data _null_; 
         set origins;
             call symput('org'!!trim(_N_), strip(origin));
         run;

         %do i=1 %to &no_of_org;  
             proc export data=&dsnin (where=(Origin="&&org&i"))
                  outfile="/path/cars.xlsx"
                  DBMS=xlsx replace;
                  sheet="&&org&i";
             run;
         %end;
%mend export;
%export(sashelp.cars);

[andreas_lds]

You can do this with ods excel without any macro-code, but the names of the sheets may not fit your requirements.

Demo:

proc sort data=sashelp.cars out=work.cars;
   by origin;
run;

ods excel file="cars.xlsx" options(sheet_interval='bygroup');

proc print data=work.cars;
   by Origin;
run;

ods excel close;

[data_null__]

You can add the SHEET_NAME option with #BYVAL see usage note. Usage Note 56543: The Excel worksheet

options byline=0;
ods excel file="cars.xlsx" options(sheet_interval='bygroup' sheet_name="#byval1");
proc print data=work.cars;
   by Origin;
   run;
ods excel close;
options byline=1;

---

@andreas_lds wrote:

You can do this with ods excel without any macro-code, but the names of the sheets may not fit your requirements.

Demo:

proc sort data=sashelp.cars out=work.cars;
   by origin;
run;

ods excel file="cars.xlsx" options(sheet_interval='bygroup');

proc print data=work.cars;
   by Origin;
run;

ods excel close;

---

[Ksharp · Accepted solution]

Libname statement ?

libname x xlsx 'c:\temp\x.xlsx';

data x.asia;
 set sashelp.cars (where=(Origin='Asia'));
run;

data x.usa;
 sashelp.cars (where=(Origin='USA'));
run;

[Ronein]

Hello and thank you for everyone.

Please find here the all solutions that you provided  and one solution that I added.

Which way do you think is the best?

Which way provides us the best ability to control formats in excel file?

Is there a more clever way to use way9(ods tagsets.excelxp) without writing 3 proc reports (Can we write one proc report with BY statement)?

 /*Way1*/
proc export data=sashelp.cars (where=(Origin='Asia'))
outfile="/path/carsRRR1.xlsx"
DBMS=xlsx replace;
sheet='Asia';
run;
proc export
data=sashelp.cars (where=(Origin='Europe'))
file="/path/carsRRR1.xlsx"
DBMS=xlsx replace;
sheet='Europe';
run;
proc export
data=sashelp.cars (where=(Origin='USA'))
file="/path/carsRRR1.xlsx"
DBMS=xlsx replace;
sheet='USA';
run;

/*Way2*/
%macro excel_export(origin);
proc export data=sashelp.cars (where=(Origin="&origin."))
outfile="/path/carsRRR2.xlsx"
DBMS=xlsx replace;
sheet="&origin.";
run;
%mend;
%excel_export(Asia)
%excel_export(Europe)
%excel_export(USA)

/*Way3*/
proc sort data=sashelp.cars (keep=origin) out=control nodupkey;
by origin;
run;
proc sql noprint;                              
 select origin into :vector separated by '+'
 from control;
quit;
%put &vector.;

PROC SQL Noprint;
select count(*)  INTO:n 	   
from control
;
QUIT;
%put &n.;


%macro mmacro;
%DO i=1  %TO  &n.;
%let X=%scan(&vector.,&i.,+);
proc export data=sashelp.cars (where=(Origin="&x."))
outfile="/path/carsRRR3.xlsx"
DBMS=xlsx replace;
sheet="&x.";
run;
%end;
%mend;
%mmacro; 


/*Way4*/
/*names of the sheets will be:Bygroup1-Asia,Bygroup1-Europe,Bygroup1-USA*/
proc sort data=sashelp.cars out=cars;
by origin;
run;

ODS excel  file="/path/carsRRR4.xlsx"
options (sheet_interval='bygroup');
proc print data=cars noobs;
by origin;
Run;
ODS excel close;


/*Way5*/
/*Here we used SHEET_NAME option with #BYVAL */
/*Names of sheets will be: Asia,Europe,USA*/
proc sort data=sashelp.cars out=cars;
by origin;
run;

options byline=0;
ods excel file="/path/carsRRR5.xlsx" 
options(sheet_interval='bygroup' sheet_name="#byval1");
proc print data=work.cars  noobs;
by Origin;
run;
ods excel close;
options byline=1;


/*Way6*/
proc sort data=sashelp.cars (keep=origin) out=control  nodupkey;
by origin;
run;

%macro excel_export(origin);
proc export data=sashelp.cars (where=(Origin="&origin."))
outfile="/path/carsRRR6.xlsx"
DBMS=xlsx replace;
sheet="&origin.";
run;
%mend;


data _null_;
set control;
call execute('%nrstr(%excel_export(' !! strip(origin) !! '))');
run;

/*Way7*/
proc sql;
create table control as
select distinct origin
from sashelp.cars;
quit;

%let no_of_org = &sqlobs;
%put &no_of_org;


data _null_; 
set control;
call symput('org'||left(_n_),strip(origin));
run;
%put &org1;
%put &org2;
%put &org3;


%macro export;
%do i=1 %to &no_of_org;  
proc export data=sashelp.cars (where=(Origin="&&org&i"))
outfile="/path/carsRRR7.xlsx"
DBMS=xlsx replace;
sheet="&&org&i";
run;       
%end;
%mend export;
%export;

/*Way8*/

libname carsRRR8 xlsx "/path/carsRRR8.xlsx";
data carsRRR8.asia;
 set sashelp.cars (where=(Origin='Asia'));
run;
data carsRRR8.usa;
 set sashelp.cars (where=(Origin='USA'));
run;
data carsRRR8.Europe;
set sashelp.cars (where=(Origin='Europe'));
run;


/*Way9*/
filename myfile "/path/carsRRR12.XML";
ods tagsets.excelxp body=myfile STYLE=HTMLBLUE 
OPTIONS(SHEET_NAME="Asia" 
        Orientation='Landscape' 
        wraptext = 'YES' 
        sheet_interval='Proc'
        autofilter='ALL'
        FROZEN_HEADERS='YES'
		convert_percentages='YES'
		TITLE_FOOTNOTE_WIDTH='20'
		EMBEDDED_TITLES='YES' 
		EMBEDDED_FOOTNOTES='YES'
		pagebreaks='YES'
		gridlines='YES' 
		PAGE_ORDER_ACROSS='YES'
		contents='NO'
		gridlines='YES');
/*** sheet 1 ***/
title;
proc report data=sashelp.cars(where=(Origin='Asia'));
Run; 
/*** sheet  2 ***/
ods tagsets.excelxp OPTIONS(SHEET_NAME="USA");
title;
proc report data=sashelp.cars(where=(Origin='USA'));
Run; 
/*** sheet  3 ***/
ods tagsets.excelxp  OPTIONS(SHEET_NAME="Europe");
title;
proc report data=sashelp.cars(where=(Origin='Europe'));
Run; 
ODS tagsets.excelxp CLOSE;  
title;
footnote;

[Kurt_Bremser]

---

@Ronein wrote:

Which way do you think is the best?

---

Maxim 4: If in Doubt, Do a Test Run.

Or in short: Try It.

Either select upon how the result looks like in the target, or what performs best (Maxim 2: Read the Log).

[Ronein]

Is it called Excel engine method?

[Ksharp]

Maybe . I don't know. You can call it whatever you want .

[Ksharp]

proc freq data=sashelp.cars noprint;
table Origin/out=levels nopercent;
run;

libname x xlsx "c:\temp\want.xlsx";
data _null_;
 set levels;
 call execute(catt('data x.',origin,';set sashelp.cars;if origin="',origin,'";run;'));
run;