topic Re: Export Multiple sheets in one file in SAS Programming

Export Multiple sheets in one file

sanjay1 — Wed, 17 Apr 2019 12:41:48 GMT

Hi Experts,

I am trying to export multiple datasets into two different sheets in same excel file. But I am getting an error that export unsuccesful.

Below is my code.

PROC EXPORT DATA= ab
OUTFILE=
"/&Main/desktop/file/ab.xlsx"
DBMS=XLSX REPLACE;
Sheet1='ab';
RUN;

PROC EXPORT DATA= ab1
OUTFILE=
"/&Main/desktop/file/ab1.xlsx."
DBMS=XLSX REPLACE;
Sheet1='ab1';
RUN;

Thanks,

Sanjay

Re: Export Multiple sheets in one file

PaigeMiller — Wed, 17 Apr 2019 12:44:36 GMT

Show us the LOG, including all of your code, and the actual error message.

Click on the {i} icon and paste the log into the window that appears. Do not paste the log into your message any other way.

Re: Export Multiple sheets in one file

andreas_lds — Wed, 17 Apr 2019 12:49:28 GMT

I can't see any error in the code you posted. Tried to export sashelp.cars and sashelp.class using proc export twice, no problem, both sheets are created as expected. Maybe an issue caused by the sas version you are using, will need to see the log,

Re: Export Multiple sheets in one file

sanjay1 — Wed, 17 Apr 2019 12:55:53 GMT

Here is the log

PROC EXPORT DATA= ab
471 OUTFILE=
472 "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx"
473 DBMS=XLSX REPLACE;
474 Sheet1='ab';
------
1
WARNING 1-322: Assuming the symbol SHEET was misspelled as Sheet1.

475 RUN;

NOTE: The export data set has 1 observations and 2 variables.
NOTE: "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.11 seconds
cpu time 0.06 seconds

476
477
478
479
480 PROC EXPORT DATA= ab1
481 OUTFILE=
482 "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx."
483 DBMS=XLSX REPLACE;
484 Sheet2='ab1';
------
1
WARNING 1-322: Assuming the symbol SHEET was misspelled as Sheet2.

485 RUN;

NOTE: The export data set has 1 observations and 2 variables.
NOTE: "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.22 seconds
cpu time 0.07 seconds

Re: Export Multiple sheets in one file

PaigeMiller — Wed, 17 Apr 2019 13:01:30 GMT

Please help us out and comply with the earlier request.

Click on the {i} icon and paste the log into the window that appears. Do not paste the log into your message any other way.

This preserves the formatting of the log and makes it easier to read.

Thanks.

Re: Export Multiple sheets in one file

andreas_lds — Wed, 17 Apr 2019 13:06:35 GMT

The option name is "sheet" not "sheet1".

Re: Export Multiple sheets in one file

ballardw — Wed, 17 Apr 2019 15:15:55 GMT

@sanjay1 wrote:

Here is the log

PROC EXPORT DATA= ab
471 OUTFILE=
472 "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx"
473 DBMS=XLSX REPLACE;
474 Sheet1='ab';
------
1
WARNING 1-322: Assuming the symbol SHEET was misspelled as Sheet1.

475 RUN;

NOTE: The export data set has 1 observations and 2 variables.
NOTE: "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.11 seconds
cpu time 0.06 seconds

476
477
478
479
480 PROC EXPORT DATA= ab1
481 OUTFILE=
482 "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx."
483 DBMS=XLSX REPLACE;
484 Sheet2='ab1';
------
1
WARNING 1-322: Assuming the symbol SHEET was misspelled as Sheet2.

485 RUN;

NOTE: The export data set has 1 observations and 2 variables.
NOTE: "C:\Users\290014098\Desktop\SAS Codes\ab.xlsx.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.22 seconds
cpu time 0.07 seconds

You may want to think about what REPLACE means.

In proc export that means it REPLACES the existing file. So you have told the code to get rid of the first file.