topic Re: Proc Export vs ODS Excel? (Creating multiple sheets with ODS excel) in SAS Programming

Proc Export vs ODS Excel? (Creating multiple sheets with ODS excel)

mcook — Tue, 13 Jul 2021 18:52:08 GMT

I am using Enterprise Guide, Version: 8.3 (8.3.0.103) (32-bit)

I Have a program similar to the following. Though my code has a few additional elements the basics are below.

Basically it loops through combinations of SubList and TableList, checks if the table exists, and if it does. exports it to its own sheet in an excel file.

( I typed this fairly quickly, so there may be syntax errors)

libname LibPath "C:\Folder1";
%LET PATH = C:\Folder2;

%LET SubList = Sub1 Sub2 Sub3 Sub4 Sub5 Sub6 Sub7 Sub8 Sub9 Sub10;
%LET TableList =	Table1 Table2 Table3 Table4;

%MACRO One();
	%DO J = 1 %TO %SYSFUNC(CountW(&SubList.));
		%LET CurrentSub = %SCAN(&SubList.,&J.);

		%DO K = 1 %TO %SYSFUNC(CountW(&TableList.));
			%LET CurrentTable = %SCAN(&TableList.,&K.);

			%IF %SYSFUNC(EXIST(LibPath.&CurrentSub._&CurrentTable.)) %THEN
				%DO;

					proc sql noprint;
						select Count(*) into :CountObs Trimmed
							from LibPath.&CurrentSub._&CurrentTable;
					quit;

					%IF &CountObs. ^= 0 %THEN
						%DO;

							Proc Export data=LibPath.&CurrentSub._&CurrentTable.
								OutFile="&Path.\OutTable.xslx"
								replace
								DBMS=xlsx;
								SHEET=&CurrentSub._&CurrentTable.;
							run;

						%END;
				%END;
		%END;
	%END;
%MEND One;

%ONE();

However, Towards the end of the loops, as the excel file gets larger, the Export times increase.

And as this program will be appended to an existing program that already takes 2 or more hours to run, i would like to minimize the run time of this program.

So, is it possible to achieve this Excel file using ODS Excel? and if so, would it be faster than proc export?

Re: Proc Export vs ODS Excel? (Creating multiple sheets with ODS excel)

PaigeMiller — Tue, 13 Jul 2021 18:59:52 GMT

I haven't gone through your code, but one thing to know about ODS EXCEL is that each time the program runs and comes to ODS EXCEL FILE= ..., a new Excel file is created (and if there was one with the same name, it is gone). Also, with ODS EXCEL, you cannot just write to one tab of an existing table, there really is no ODS EXCEL equivalent to the REPLACE option in PROC EXPORT.

Re: Proc Export vs ODS Excel? (Creating multiple sheets with ODS excel)

ballardw — Tue, 13 Jul 2021 19:38:14 GMT

I think a LIBNAME xlsx might be more appropriate. Then you are just copying the data sets into a library.

Your libname statement might look like

Libname Mylib xlsx "&Path.\OutTable.xslx";

The sheet names would be the name of the data set. Proc copy or any of your favorite ways to move data sets should work.

Make sure to clear the library reference after you write the last data set. Otherwise SAS is still using the file and other programs or users won't be able to use it.

Re: Proc Export vs ODS Excel? (Creating multiple sheets with ODS excel)

SASKiwi — Tue, 13 Jul 2021 20:16:45 GMT

In my experience ODS is resource intensive so I wouldn't use it for large spreadsheets because it likely to be slower. PROC EXPORT or LIBNAME with the XLSX engine are preferable options.