topic Proc export BY to many excel sheets in same file in SAS Programming

Proc export BY to many excel sheets in same file

Ronein — Mon, 29 Jul 2019 07:26:59 GMT

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;

Re: Proc export BY to many excel sheets in same file

Kurt_Bremser — Mon, 29 Jul 2019 07:41:08 GMT

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;

Re: Proc export BY to many excel sheets in same file

Shmuel — Mon, 29 Jul 2019 07:48:36 GMT

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

Re: Proc export BY to many excel sheets in same file

andreas_lds — Mon, 29 Jul 2019 10:41:24 GMT

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;

Re: Proc export BY to many excel sheets in same file

data_null__ — Mon, 29 Jul 2019 11:55:10 GMT

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;

Re: Proc export BY to many excel sheets in same file

Ksharp — Mon, 29 Jul 2019 12:18:44 GMT

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;

Re: Proc export BY to many excel sheets in same file

Ronein — Tue, 30 Jul 2019 04:33:55 GMT

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;

Re: Proc export BY to many excel sheets in same file

Kurt_Bremser — Tue, 30 Jul 2019 05:22:47 GMT

@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).

Re: Proc export BY to many excel sheets in same file

Ronein — Sat, 27 Nov 2021 10:30:23 GMT

Is it called Excel engine method?

Re: Proc export BY to many excel sheets in same file

Ksharp — Sat, 27 Nov 2021 10:38:25 GMT

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

Re: Proc export BY to many excel sheets in same file

Ksharp — Sat, 27 Nov 2021 10:47:03 GMT

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;