export datasets into multiple sheets of one excel file in sas

Question

I'm use this code

proc export data=goldsheet_invalid outfile="C:\Documents and Settings\sasadm\Desktop\gold.xls" dbms=xls replace;
sheet="gold";
run;

proc export data=platinumsheet_invalid outfile="C:\Documents and Settings\sasadm\Desktop\gold.xls" dbms=xls replace;
sheet="platinum";
run;

proc export data=titaniumsheet_invalid outfile="C:\Documents and Settings\sasadm\Desktop\gold.xls" dbms=xls replace;
sheet="titanium";
run;

Error:Statement is not valid or it is used out of proper order

Note:- already try dbms=xlsx or dbms=EXCELCS but not work

Nothing wrong with your code that I can see. What is the error pointing to in the log? Does any dbms=xls work (without the sheet names etc.) or do you perhaps have some problem with your excel installation? — Joe
dbms=xls work without the sheetname but after write sheetname we got the error — bhavnish
Okay, but what else is there in the error? It should show you what it's complaining about; and, I wouldn't be shocked to see more errors than that one line. You might also add what SAS version you're on. SAS 9.4 has no problem with running that code sheet included. — Joe
Error:-Statement is not valid or it is used out of proper order.i am using sas 9.1 — bhavnish
In any event, you're using SAS 9.1, that's ten plus years out of date. I am not sure if 9.1 supports sheet in dbms=xls. Upgrade. — Joe

nvlm nvlm · Accepted Answer · 2017-11-14T15:25:01

Instead of using a PROC EXPORT this can be accomplished with older versions of SAS using ODS (Output Delivery System) statements. Going this route is not as clean as the PROC EXPORT but if all you want is to get the data from these data sets to a single Excel workbook and have the results of each proc statement on a different worksheet this will do it.

In this case the code to accomplish what you are looking for would be:

ods tagsets.excelxp file='C:\temp\gold.xml' options(sheet_name = 'Gold' sheet_interval='proc');
proc print data=goldsheet_invalid;
run;
ods tagsets.excelxp options(sheet_name = 'Platinum');
proc print data=platinumsheet_invalid;
run;
ods tagsets.excelxp options(sheet_name = 'Titanium');
proc print data=titaniumsheet_invalid;
run;
ods tagsets.excelxp close;

You will notice that the file extension created is XML, this is a necessity. When you load the file in Excel is would appear as expected and feel free to update the file extension from there.

More details about SAS and ODS can be found at: https://support.sas.com/rnd/base/ods/odsmarkup/TipSheet_ods_xl_xp.pdf

export datasets into multiple sheets of one excel file in sas

1 Answers