topic Re: Export multiple datasets to Excel workbook in SAS Programming

Export multiple datasets to Excel workbook

nayab_shaik — Thu, 04 Jul 2019 12:42:08 GMT

Hi Team,

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

Advance Thanks

Re: Export multiple datasets to Excel workbook

PeterClemmensen — Thu, 04 Jul 2019 12:43:44 GMT

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

Re: Export multiple datasets to Excel workbook

nayab_shaik — Thu, 04 Jul 2019 12:43:09 GMT

yes

Re: Export multiple datasets to Excel workbook

PeterClemmensen — Thu, 04 Jul 2019 12:42:38 GMT

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-workbook/td-p/426861

Re: Export multiple datasets to Excel workbook

Kurt_Bremser — Thu, 04 Jul 2019 12:45:10 GMT

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

Re: Export multiple datasets to Excel workbook

Reeza — Thu, 04 Jul 2019 15:02:53 GMT

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

Re: Export multiple datasets to Excel workbook

koyelghosh — Thu, 04 Jul 2019 15:40:02 GMT

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.

Re: Export multiple datasets to Excel workbook

ChrisNZ — Fri, 05 Jul 2019 05:39:37 GMT

Shouldn't this work?

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

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

Re: Export multiple datasets to Excel workbook

andreas_lds — Fri, 05 Jul 2019 06:04:59 GMT

@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.

Re: Export multiple datasets to Excel workbook

ChrisNZ — Fri, 05 Jul 2019 07:03:19 GMT

That makes sense of course. No metadata in Excel.

Re: Export multiple datasets to Excel workbook

koyelghosh — Fri, 05 Jul 2019 13:51:36 GMT

@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.

Re: Export multiple datasets to Excel workbook

nayab_shaik — Fri, 05 Jul 2019 17:01:12 GMT

Thanku you