proc export sheet=

[Steelers_In_DC]

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,

[Reeza]

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]

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]

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]

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

[zana]

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]

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

[zana]

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 · Accepted solution]

dbms = excelcs fixes the issue.