topic Re: proc export sheet= in SAS Programming

proc export sheet=

Steelers_In_DC — Thu, 20 Nov 2014 21:29:44 GMT

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,

Re: proc export sheet=

Reeza — Thu, 20 Nov 2014 21:33:08 GMT

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 maintenance release of SAS® 9.3 (TS1M1)

Re: proc export sheet=

Steelers_In_DC — Fri, 21 Nov 2014 15:03:09 GMT

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.

Re: proc export sheet=

RW9 — Fri, 21 Nov 2014 15:14:21 GMT

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.

Re: proc export sheet=

Steelers_In_DC — Fri, 21 Nov 2014 15:19:09 GMT

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

Re: proc export sheet=

zana — Sun, 23 Nov 2014 22:05:02 GMT

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;

Re: proc export sheet=

Steelers_In_DC — Mon, 24 Nov 2014 14:34:28 GMT

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

Re: proc export sheet=

Steelers_In_DC — Mon, 24 Nov 2014 14:42:33 GMT

dbms = excelcs fixes the issue.

Re: proc export sheet=

zana — Tue, 25 Nov 2014 07:14:28 GMT

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