Export to multiple XLSX files and multiple sheets in Each file

Ronein — Thu, 10 Jun 2021 08:28:51 GMT

Hello,

I want to create multiple XLSX files and in each file multiple sheets.

I want to work on data set sashelp.cars.

For each "Make" to have a seprate XLSX file with name of the make(for example: File Audi,BMW,Cadillac and so on).

Within Each file there will be multiple folder by "Type" (For example:Sedan,SUV,Sports and so on).

What is the best way to do it please and may you please show full code?

In each sheet there will be raw data for this Make+Type.

This vode here is not good because it create only one XLSX file with multiple sheets (by type+make).

I want to create multiple XLSX files and in each file multiple sheets by type

proc sort data=sashelp.cars out=work.cars;
   by Type Make;
run;
ods excel file="/path/CCC.xlsx"
options(sheet_interval='bygroup');
proc print data=work.cars;
 by Type Make;
run;
ods excel close;

Thank you

Re: Export to multiple XLSX files and multiple sheets in Each file

Ronein — Thu, 10 Jun 2021 08:53:46 GMT

proc sort data=SASHELP.cars;
by made type;
run;


PROC SQL noprint;
	select  distinct made into :Vector separated by '+'	   
	from  SASHELP.cars
;
QUIT;
%put &Vector;
%let n= %sysfunc(countw(&Vector));
%put &n;

%macro exportMacro; 
%do j=1 %to &n.;
%let made=%scan(&Vector.,&j.,+);
ods excel file="/path/made_&made..xlsx"
options(sheet_interval='bygroup');
proc print data=SASHELP.cars(where=(made=&made.));
by type;
run;
ods excel close;
%end;
%mend;
%exportMacro;

This code works 100% fine for me but my question if there is a more coefficient way to do it without create macro var

topic Export to multiple XLSX files and multiple sheets in Each file in SAS Programming

Export to multiple XLSX files and multiple sheets in Each file

Re: Export to multiple XLSX files and multiple sheets in Each file