The SAS Output Delivery System and reporting techniques

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

Reply
N/A
Posts: 0

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

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
SAS Super FREQ
Posts: 8,868

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

Posted in reply to deleted_user
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
Occasional Contributor
Posts: 6

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

Posted in reply to deleted_user
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
Contributor
Posts: 56

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 4 replies
  • 221 views
  • 0 likes
  • 4 in conversation