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;
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;
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;
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);
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;
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;
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;
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;
@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).
Is it called Excel engine method?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.