Export multiple datasets to Excel workbook

[nayab_shaik]

Hi Team,

how can you export 50 data sets into single excel workbook with different sheets.

Advance Thanks

[PeterClemmensen]

Are the data sets in the same library? And how do you determine the names of the data sets?

[nayab_shaik]

yes

[PeterClemmensen]

There are a few examples to get you going here

https://communities.sas.com/t5/SAS-Programming/How-do-i-Export-Multiple-Sas-Datasets-into-ms-excel-w...

[Reeza]

You can use ODS EXCEL.
If you need more details, please provide more details about your problem. Vague questions will get vague answers.

[Kurt_Bremser]

Please use more descriptive subject lines in the future. "sas" is NOT descriptive. 

[koyelghosh]

You can try the below. Just change the OUTFILE= with the full path of the file (in which you want all the results to go). Please note this macro may not work if you have more than 255 sheets to be exported. LIBNAME is the library/folder where all your sas datasets are there.

%MACRO ExportAllDatasetsToExcel(LIBNAME, Extension="XLS");

	PROC SQL NOPRINT;
	CREATE TABLE columns as
	SELECT DISTINCT(memname) AS table_name FORMAT=$30.
	FROM dictionary.columns
	WHERE libname = &LIBNAME
	;
	quit;
	
	PROC SQL NOPRINT;
		SELECT COUNT(*) AS NumObs INTO :NumObs FROM columns;
	RUN;

	%DO I=1 %TO &NumObs;
		DATA _NULL_ ;
			SET Columns (FIRSTOBS=&I OBS=&I);
			CALL SYMPUT('TableName',TRIM(table_name));
		RUN;
		%PUT "&TableName";
		PROC EXPORT DATA=&LIBNAME..&TableName DBMS=XLSX OUTFILE="FullPathOfTheFileHere.xlsx";
		SHEET="&TableName";
		RUN;
	%END;
%MEND;

Please let me know if this worked for you. I am just curious.

[nayab_shaik]

Thanku you

[ChrisNZ]

Shouldn't this work?

libname XL xlsx "%sysfunc(pathname(WORK))\T.xlsx";

proc copy inlib=WORK outlib=XL mt=data; run;

[andreas_lds]

---

@ChrisNZ wrote:

Shouldn't this work?

libname XL xlsx "%sysfunc(pathname(WORK))\T.xlsx";

proc copy inlib=WORK outlib=XL mt=data; run;

---

Works perfect, with one minor glitch:

WARNING: Engine XLSX does not support SORTEDBY operations.  SORTEDBY information cannot be copied.

[ChrisNZ]

That makes sense of course. No metadata in Excel.

[koyelghosh]

@ChrisNZ Works very nicely. Your code is simple and elegant.

I read earlier but I forgot @Kurt_Bremser advice (his Maxim 11 of 52 ... that a Macro is not needed) and the result is a negative control of an experiment (wherein the positive control being someone pursuing reuse of existing code and elegance).

Lesson: Use BASE SAS first. So much is already there. Perhaps many times, one can spare reinventing the wheel.