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,
dbms = excelcs fixes the issue.
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:
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.
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.
I have been using ods tagsets.excelxp but wanted to try export or download to test performance.
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;
I get an error that says dblabel is not a valid option.
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
dbms = excelcs fixes the issue.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.