topic PROC EXPORT -- Excel to multiple sheet in SAS Programming

PROC EXPORT -- Excel to multiple sheet

gabonzo — Thu, 10 Feb 2022 15:31:22 GMT

Hi,

I know that this question has been posted several times over the years, but none of the accepted solutions work for me.

I am trying to create an Excel workbook with three different sheets, from three different datasets.

The documentation for PROC EXPORT says to

Omit REPLACE and add the new sheet name

Which I do, and here's my code:

%macro output_mismatch(outFile=, debug=0);

	%local i lvl;
	
	%do i=1 %to &nlevels.;
		%let lvl=%scan(&geovar., &i.);

		%if %nobs(data=&lvl._mismatch) NE 0 %then %do;
			proc export dbms=xlsx 
                data=&lvl._mismatch(keep=&lvl. rename=(&lvl.=&lvl._uid)) 
                outfile=&outFile.;
				sheet="&lvl.";
			run;
		%end;
	%end;

%mend;

By checking the log file, I can see that the procedure is executed three times:

NOTE: The export data set has 28682 observations and 1 variables.
NOTE: "OUT_MIS" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.18 seconds
      user cpu time       0.15 seconds
      system cpu time     0.01 seconds
      memory              2900.50k
      OS Memory           34192.00k
      Timestamp           2022-02-10 10:28:00 AM
      Step Count                        314  Switch Count  0

NOTE: The export data set has 1904 observations and 1 variables.
NOTE: "OUT_MIS" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.04 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              2896.75k
      OS Memory           34192.00k
      Timestamp           2022-02-10 10:28:00 AM
      Step Count                        315  Switch Count  0

NOTE: The export data set has 653 observations and 1 variables.
NOTE: "OUT_MIS" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.03 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              2898.06k
      OS Memory           34192.00k
      Timestamp           2022-02-10 10:28:00 AM
      Step Count                        316  Switch Count  0

But when I open the Excel file, only the last sheet is present, the other two have been overwritten.
My SAS version is 9.4 M7

Re: PROC EXPORT -- Excel to multiple sheet

ballardw — Thu, 10 Feb 2022 16:08:25 GMT

You don't describe at all why any of the other solutions "don't work for you", or even which solutions you tried.

One thing to remember about Proc Export is that the intent is to create single files. Your log shows you that with each call to the Proc creating the same file name.

The LIBNAME XLSX is probably likely the better solution for this.

Re: PROC EXPORT -- Excel to multiple sheet

gabonzo — Thu, 10 Feb 2022 16:19:31 GMT

Forget it, you know what? I have posted the same question three months ago:

https://communities.sas.com/t5/SAS-Programming/Excel-export-to-multiple-sheets-not-working-with-filerefs/m-p/767060

So the reason seems to be that PROC EXPORT doesn't work well with file references, but it works if you hardcode the file path.

Re: PROC EXPORT -- Excel to multiple sheet

gabonzo — Wed, 16 Feb 2022 15:03:58 GMT

Known bug: https://support.sas.com/kb/68/654.html