topic Re: How to export the different dataset into different excel sheets in to o in ODS and Base Reporting

How to export the different dataset into different excel sheets in to one

deleted_user — Mon, 22 Oct 2007 11:07:48 GMT

Hi,

Any one knows how to export different data sets in to one Excel file in different sheets
its urgent plz if anyone knows the answer
thanks
srinu

Re: How to export the different dataset into different excel sheets in to one

Cynthia_sas — Mon, 22 Oct 2007 15:46:05 GMT

The method that you use for this will depend on whether you're using PROC EXPORT or the LIBNAME engine for Excel and whether you have SAS/Access for PC File Formats installed.

Your best bet for help with this task is to contact Tech Support.

cynthia

Re: How to export the different dataset into different excel sheets in to o

MarkRoosen — Tue, 23 Oct 2007 06:45:08 GMT

Hi,

if you do not have any licenses for export to Excel you can create XML files that Excel can reads (with multiple sheets)
Basic information you can find on
http://support.sas.com/rnd/base/topics/odsmarkup/tableeditor/index.html

Remark: for a lot of data these files will be huge ! !

a quick example could be: ( you need the file tableeditor.tpl' that you can find in the URL mentioned above
=============================================================================

%include 'c:\temp\tableeditor.tpl';
ods tagsets.tableeditor file="c:\temp\export_naar_excel.html"
style=styles.mystyle
options(excel_zoom="90"
excel_scale="90"
excel_autofilter="yes"
excel_frozen_headers="yes"
excel_orientation="landscape"
sheet_name="first,second,third"
excel_table_move="1,2,3"
auto_format="color1"
excel_save_file="c:\\ temp.xls"
);
proc print data=sashelp.class;
run;
proc report data=sashelp.class nowd;
run;
proc print data=sashelp.orsales;
run;
ods tagsets.tableeditor close;

best regards,
Mark Roosen

Re: How to export the different dataset into different excel sheets in to one

Kwok — Tue, 30 Oct 2007 12:21:11 GMT

Here is an exmaple I always use:

Libname in ;

Filename odsout

ods listing close; run;
ods tagset.excelXP file=odsout ;
proc print data=in.one ;
run;
ods tagset.excelXP ;
proc print data=in.two;
Run;
ods tagset.excelXP ;
proc report data=in.three;
run;
....
ods _all_ close; run;
ods losting; run;
The trick is for each seperate sheet, you need a ods statement. However, only the 1st ods statement define the output( file=odsout). If every ods statment has a file statement, then it will be overwritten by the next ods with (file=).
If you want each sheet to have a different sheet name, then use the sheet_name="" as the ODS option.

Hope this help.

Regards
David

Re: How to export the different dataset into different excel sheets in to one

deleted_user — Wed, 31 Oct 2007 14:55:04 GMT

I am also trying to move data from SAS into an Excel 2003 workbook. The workbook is pre-defined and fairly complex. The current process uses ADO and VBA code to populate the data. This process is slow on our server , si I have been experimenting with alternates. I would need to populate defined Excel named ranges and am trying to avoid the R1C1 format for flexability.

The Excel Libname Engine sounds like it should work, but is corrupting the workbook. Here is the sample code.

Libname XLSLib clear;
Libname XLSLib Excel "&XLSFile2." SCAN_TEXT=NO Header=NO VER=2002;

Proc SQL NOPRINT;

Create Table Work.Tmp99 as
Select 0 as F1
From SD.Dual as dTHRw
Union All
Select 0 as F1
From SD.Dual as dDDDW
Union All
Select Count(*) as F1
From Work.Tmp01
Union All
Select Count(*) as F1
From Work.tmp02
Union All
Select Count(*) as F1
From Work.tmp03

Quit;

Proc append
Base = XLSLib.Counters
Data = Work.Tmp99;
Run;

Libname XLSLib clear;

Any ideas? Is the Range option available in ODS?

Thanks,
Tom