proc export sheet=

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

proc export sheet=

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,


Accepted Solutions
Solution
‎11-24-2014 09:42 AM
Valued Guide
Posts: 858

Re: proc export sheet=

dbms = excelcs fixes the issue.

View solution in original post


All Replies
Super User
Posts: 17,881

Re: proc export sheet=

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

Valued Guide
Posts: 858

Re: proc export sheet=

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.

Super User
Super User
Posts: 7,413

Re: proc export sheet=

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.

Valued Guide
Posts: 858

Re: proc export sheet=

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

Frequent Contributor
Posts: 81

Re: proc export sheet=

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;

Valued Guide
Posts: 858

Re: proc export sheet=

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

Frequent Contributor
Posts: 81

Re: proc export sheet=

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

Solution
‎11-24-2014 09:42 AM
Valued Guide
Posts: 858

Re: proc export sheet=

dbms = excelcs fixes the issue.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 615 views
  • 3 likes
  • 4 in conversation