topic Export all the datasets from work library in excel format with datasets name as excel sheet names in New SAS User

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

chandan_mishra — Wed, 21 Aug 2019 15:25:47 GMT

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

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

Reeza — Wed, 21 Aug 2019 15:35:46 GMT

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

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

chandan_mishra — Wed, 21 Aug 2019 16:09:31 GMT

Thanks Reeza. This works.