export 2 datasets to one spreadsheet 1

[a0f6459]

I have two datasets that I want to display in one spreadsheet but two separate worksheets.

The below does not work because I get the error:

"DBMS type excel2003 not valid for export"

Is there any other way to do it?

proc export
data=test
outfile="c:\temp\results.xls"
dbms=Excel2003;
run;
proc export
data=test2
outfile="c:\temp\results.xls"
dbms=Excel2003;
run;

Thanks!

 

[ChrisW75]

Yup.
You can use ODS TAGSETS.EXCELXP to write data out to an Excel Spreadsheet. You'll need version 9 of SAS to do it like this I think.

ods tagsets.excelxp file="P:\Clients\Active\xxxx\reports\xxxx_&RUNDT..xls"
    options(absolute_column_width='10,10,10,10,8,8,8,8,8,8,8,8,8,8'
            row_repeat='header'
            embedded_titles='yes'
            frozen_headers='yes'
            scale='100' 
            orientation='landscape'
            sheet_name='Report'
           )
   style=styles.psexcel;

proc report data=test nowd;
  columns ....
  define .....
run;

proc report data=test2 nowd;
  columns ....
  define .....
run;

ods tagsets.excelxp close;

You can use additional ODS excelXP options(....); statements between the two datasets to set columns widths, sheet names etc differently.
Also you can obviously change proc report to proc print or tabulate or any other output procedure you want.

Enjoy.
Chris.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[a0f6459]

This seems so useful however do I have to have ExcelXP to use it? I do have SAS 9, but only Excel 2003.

What do you suggest Chris?

Thanks

Andrew

 

[a0f6459]

never mind

let me give it a shot

 

[ChrisW75]

It will work with 2003, that's what I've got. It's just the tagset that is called ExcelXP. I think that 2003 is pretty much the version that came out around the time that XP came out.

One thing to watch out for though. The tagset does not work with earlier versions of Excel. This is because it doesn't ACTUALLY create an Excel spreadsheet file. It creates an XML document (which you'll recognise when you open the file then try to save it, it'll have "Save as XML document" selected) which is formatted for Excel to read. If you're sending the spreadsheet out to external clients, it is worthwhile opening the document and saving it as an Excel spreadsheet. I actually wrote a VBA macro to do this for me, as well as taking care of some last minute fomatting tweaks.
Also, the XML document is considerably larger than the XLS document of the same data.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[dblan]

I use proc export all the time to send different datasets to the same spreadsheet. This works fine for Office 2003 for me.

proc export
data = test
outfile = "c:\temp\results.xls"
dbms = excel replace;
sheet = "data1";
run;
proc export
data = test2
outfile = "c:\temp\results.xls"
dbms = excel replace;
sheet = "data2";
run;