BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gabonzo
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
gabonzo
Quartz | Level 8

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

 

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.

 

 

View solution in original post

3 REPLIES 3
ballardw
Super User

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.

gabonzo
Quartz | Level 8

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

 

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.

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4581 views
  • 3 likes
  • 2 in conversation