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

I am trying to use proc export to have multiple sheets to one excel file.  I have seen several instances of this topic in this forum and elsewhere but my issue continues.

I am using:

proc export

     data=work.test

     outfile="C:\Users\pp78499\Desktop\tests.xlsx:"

     dbms=xlsx replace;

     sheet="sheet1";

run;

proc export

     data=work.test

     outfile="C:\Users\pp78499\Desktop\tests.xlsx:"

     dbms=xlsx replace;

     sheet="sheet2";

run;

Both exports work but the second sheet overwrites the first. I have tried switching up the dbms name or removing replace but it doesn't help.  Any suggestions are appreciated.

Thank You,

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

dbms = excelcs fixes the issue.

View solution in original post

8 REPLIES 8
Reeza
Super User

Have you tried not including replace in the second proc?

EDIT: Also what version of SAS do you have, if 9.3 then you may need a hotfix:

51580 - The XLSX engine is enhanced to write multiple sheets per Microsoft Excel file in the first m...

Steelers_In_DC
Barite | Level 11

I have tried removing replace in the second, I get an error that the file already exists.  I have looked at the hotfix but have never used those before and am hesitant to use it because of my lack of related knowledge.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Just to add.  There are better methods available to you to get data out into Excel.  for instance:

ods tagsets.excelxp file="xyz.xlsx";

ods tagsets.excelxp options=(sheet_name="Sheet1");

proc report data=...;

ods tagsets.excelxp options=(...;

proc report ...;

ods _all_ close;

You also have the libname to Excel if you want straight data to ranges.

Any particular reason for using export?  If its because it doesn't create a native file, this is covered in other posts.  tagsets generate XML which Excel (amongst others) can read and parse fine.  A warning pops up when the file is xlsx but not in Open Document format - it does not cause any problems however.  You can of course, learn Open Office Docment structure, folders/XML docs all zipped up in the .xlsx file if you like.  Then you can write it by hand in SAS.  It gives a lot of control, but is also a lot of hassle.

If you are using the output for anything other than review (and even then I wouldn't) then your better off with another format anyways as Excel is not really for <insert what you use it for> task.

Steelers_In_DC
Barite | Level 11

I have been using ods tagsets.excelxp but wanted to try export or download to test performance.

zana
Calcite | Level 5

You can try this one:

libname a "C:\Users\pp78499\Desktop\tests.xls";

   data a.sheet1(dblabel=no);

    set work.test;

  run;

libname a clear;

libname a "C:\Users\pp78499\Desktop\tests.xls";

   data a.sheet2(dblabel=no);

    set work.test;

  run;

libname a clear;

Steelers_In_DC
Barite | Level 11

I get an error that says dblabel is not a valid option.

zana
Calcite | Level 5

It was correct for me. However, please remove dblabe statement.

libname a "C:\Users\pp78499\Desktop\tests.xls";

   data a.sheet1;

    set work.test;

  run;

libname a clear;

libname a "C:\Users\pp78499\Desktop\tests.xls";

   data a.sheet2;

    set work.test;

  run;

libname a clear;

Good luck

Zana

Steelers_In_DC
Barite | Level 11

dbms = excelcs fixes the issue.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 8 replies
  • 6809 views
  • 3 likes
  • 4 in conversation