Export all the datasets from work library in excel format with datasets name as excel sheet names

[chandan_mishra]

Hello

I want to export all the tables from work library in excel format with dataset names as sheet names:

%let path = "C:\Users\Data";



PROC EXPORT DATA= Data1 OUTFILE="&path.\Forecast.xlsx" DBMS=xlsx REPLACE;
SHEET='Data1';
RUN;


PROC EXPORT DATA= SecondData OUTFILE="&path.\Forecast.xlsx" DBMS=xlsx REPLACE;
SHEET='SecondData';
RUN;



PROC EXPORT DATA= Data3 OUTFILE="&path.\Forecast.xlsx" DBMS=xlsx REPLACE;
SHEET=''Data3";
RUN;

As can be seen, I have to write the same code multiple times to export the data in the same excel file with dataset name as sheet names. Is there any way, I can write one Proc export and just export all the datasets available in the work library in the excel format with dataset names as sheet names.

Thanks

Chandan Mishra

[Reeza · Accepted solution]

Yes, use a libname instead to create the Excel file and PROC COPY to copy the data sets from the input library, WORK in your example, to the new file, outfile. 

libname outfile xlsx "&path.\Forecast.xlsx";

proc copy in=work out=outfile;
run;

libname outfile;

---

@chandan_mishra wrote:

Hello

I want to export all the tables from work library in excel format with dataset names as sheet names:

%let path = "C:\Users\Data";



PROC EXPORT DATA= Data1 OUTFILE="&path.\Forecast.xlsx" DBMS=xlsx REPLACE;
SHEET='Data1';
RUN;


PROC EXPORT DATA= SecondData OUTFILE="&path.\Forecast.xlsx" DBMS=xlsx REPLACE;
SHEET='SecondData';
RUN;



PROC EXPORT DATA= Data3 OUTFILE="&path.\Forecast.xlsx" DBMS=xlsx REPLACE;
SHEET=''Data3";
RUN;

As can be seen, I have to write the same code multiple times to export the data in the same excel file with dataset name as sheet names. Is there any way, I can write one Proc export and just export all the datasets available in the work library in the excel format with dataset names as sheet names.

Thanks

Chandan Mishra

---

[chandan_mishra]

Thanks Reeza. This works.